2GDdjGtv

1
2SCHOOL OF COMPUTING AND INFORMATION
3TECHNOLOGY
4DATABASE APPLICATIONS LABORATORY
5BTCS14F5700
6for
7Fifth Semester
8B.Tech in Computer Science and Engineering
9(Prepared in May-2016)
10Name
11SRN
12Branch
13Semester
14Section
15Academic Year
16Database Applications Lab REVA University
17School of Computing and Information Technology Page 2
18INDEX
19SL. No Contents Page. no
201 Lab Objectives
213
222 Lab Outcomes
233
243 Lab Requirements
254
264 Guidelines to Students
275
285 Introduction to Database, oracle & SQL
296
306 List of Lab Exercises 20
317 Solutions for Lab Exercises 23
328 Lab Assignment Exercises
3359
349 Solutions for Lab Assignment Exercise I: 60
3510 Viva Voce Questions
3666
37Learning Resources and References 67
38Database Applications Lab REVA University
39School of Computing and Information Technology Page 3
401. Lab Objectives:
41The objectives of this course are to:
421. Provide hands on skills on creating a database.
432. Demonstrate different operations on database using SQL DML/DDL commands.
443. Identify and illustrate various clauses for different queries.
454. Generate reports as per user requirements
465. Develop industry standard database applications for various domains.
472. Lab Outcomes:
48On successful completion of this course; student shall be able to:
491. Design and implement a database schema.
502. Use appropriate SQL commands for designing queries
513. Design and build a GUI application using a front end tool
524. Design and develop applications like banking, reservation system, etc
53Database Applications Lab REVA University
54School of Computing and Information Technology Page 4
553. Lab Requirements
56Following are the required hardware and software for this lab, which is available in the
57laboratory.
58• Hardware: Desktop system or Virtual machine in a cloud with OS installed. Presently
59in the Lab, Pentium IV Processor having 1 GB RAM and 250 GB Hard Disk is available.
60Desktop systems are dual boot having Windows as well as Linux OS installed on them.
61• Software: The DBMS packages that fall in this category are as follows:
62o Oracle ( follows 7 rules )
63o DB2 ( follows 9 rules )
64o Ingress ( follows 10 rules )
65o Sybase ( follows 9 rules )
66Log Into Oracle
67Microsoft Windows
68Under Windows environment, the Oracle client is called SQL*Plus.The following are Steps for
69logging into the SQL.
70Steps: 1. Click Start, and then click Run.
71 2. Type sqlplus, and fill in the username, password, and database name
72 3. After you log in to SQL*Plus, you see the following message:
73Connected to: Oracle10g Enterprise Edition Release 9.1.7.0.0 - Production
74JServer Release 9.1.7.0.0 – Production and you should receive the prompt:
75Creating user: Create user <yourName> identified by <Password>;
76Where <yourName> is again your login name, and <Password> is the password you would like
77to use in the future. This command, like all other SQL commands, should be terminated with a
78semicolon.
79Changing Your Password : In response to the SQL> prompt, type
80 Alter user <username> identified by <Password>;
81where <username> is again your login name, and <Password> is the password you would like
82to use in the future. This command, like all other SQL commands, should be terminated with a
83semicolon.
84Database Applications Lab REVA University
85School of Computing and Information Technology Page 5
864. Guidelines to Students
87 Equipment in the lab for the use of student community. Students need to
88maintain a proper decorum in the computer lab. Students must use the
89equipment with care. Any damage is caused is punishable.
90 Students are required to carry their observation / programs book with
91completed exercises while entering the lab.
92 Students are supposed to occupy the machines allotted to them and are not
93supposed to talk or make noise in the lab. The allocation is put up on the lab
94notice board.
95 Lab can be used in free time / lunch hours by the students who need to use
96the systems should take prior permission from the lab in-charge.
97 Lab records need to be submitted on or before date of submission.
98 Students are not supposed to use flash drives.
99 In C3 exam one Data base application will be asked and the set of some
100queries will be given in the final exam and evaluated for 50 marks and scale
101down to 25 marks.
102Database Applications Lab REVA University
103School of Computing and Information Technology Page 6
1045. INTRODUCTION TO DATABASE, ORACLE AND SQL
105Database: -
106A group of tables with related data in them are called database.
107Database Management System: -
108DBMS consists of a collection of interrelated data and a set of programs to manage
109these data.
110Data Model:-
111Structure of database is defined by data model.
112Different data models are as follows:
113• Object Oriented model
114• Relational model
115• Network model
116• Hierarchical model
117Relational model:-
118• Relational model uses a collection of tables to represent both data and relationship
119among those tables.
120• Most database management systems are based on the relational model.
121• RDBMS follows codd’s rules.
122• There are 12 rules specified by E.F. Codd that must be satisfied by adatabase
123package for being an RDBMS.
124SQL * Plus
125SQL *Plus enables you to manipulate SQL commands and PL/SQL blocks, and to perform
126many additional tasks as well. Through SQL *Plus, you can
127• Enter, edit, store, retrieve, and SQL commands and PL/SQL blocks
128• Format, perform calculations on, store, and print query results in the form of reports
129• List column definitions for any table
130• Access and copy data between SQL databases
131• Send messages to and accept responses from an end user
132Database Applications Lab REVA University
133School of Computing and Information Technology Page 7
134Oracle Overview
135 Oracle is one the most popular Relational Database Management System (RDBMS).
136Some other famous RDBMS includes Microsoft SQL Server, Sybase, MySQL, PostgreSQL, etc.
137Essentially, all the aforementioned RDBMS employs Structural Query Language (SQL) as
138their query interface. Users usually issue their queries by SQL through a "client". Different
139RDBMS offer different forms of clients. For example, MS SQL Server offers a GUI interface
140for user to type in their SQL language, and their queries would be executed after pressing
141the "Execute" button on the client. Oracle provides both GUI client and command-line
142client. In this lesson, we will study the command-line client, SQL*Plus. In addition, Oracle
143extends the standard SQL (e.g. select * from table) with its application-specific commands
144(e.g. checking how many table you have been created in your Oracle account) into a Oracle
145specific language called PL/SQL. In this tutorial, you will interact with Oracle database, thru
146SQL*Plus, by issuing a number of PL/SQL queries.
147SQL Basics
148Structured Query Language (SQL), which is an ANSI standard language for interacting with
149relational databases, is the main tool for extracting the information.
150A database is a representation of a real-world thing called an Entity. Examples of entities
151are vehicles, employees, customers, fish, buildings, and even things such as baseball teams.
152The database stores facts about the entity in an organized framework, model, or schema.
153These facts are called attributes.
154An Instance is one occurrence of an entity.
155Each entity must have an identifier, which is one or more attributes that make each entity
156instance unique from any other instance. The identifier should contain a value that does not
157change.
158Examples of identifiers are student IDs, payroll numbers, or social security numbers.
159Primary key :- If the entity does not have an attribute that can be used as an identifier, an
160artificial identifier can be created. The identifier on an entity is often called a primary key.
161Foreign key :- A foreign key is a set of attributes of the considered table that exists as a
162primary key attributes in another table. Database records are matched (joined) through the
163use of primary and foreign keys.
164Database Applications Lab REVA University
165School of Computing and Information Technology Page 8
166Normalization:- Normalization is a process consisting of series of steps, which is used to
167group the database attributes. The purpose of this design is to ensure that the tables within
168the database are space efficient and performance efficient.
169• Zero Normal Form: Each of the relations (tables) has a unique identifier (primary
170key).
171• First Normal Form: Separate the repeating groups of attributes or multi valued
172attributes into a relation of their own. Be sure to form composite keys.
173• Second Normal Form: Establish full functional dependency by separating out
174attributes that are not fully dependent on the full primary keys.
175• Third Normal Form: Remove transitive dependencies by separating attributes that
176are dependent on a non key attribute.
177How SQL works
178The purpose of SQL is to interface to a relational database such as Oracle, and all SQL
179statements are instructions to the databases.
180SQL provides commands for a variety of tasks including:
181• Querying data
182• Inserting, updating, and deleting rows in a table
183• Creating, replacing, altering, and dropping objects
184• Controlling access to the database and its objects
185• Guaranteeing database consistency and integrity
186Data Types
187Each literal or column value manipulated by Oracle has a data type. A value’s data
188type associates a fixed set of properties with the value. These properties cause Oracle to
189treat values of one data types differently values of another.
190Character Data types:- Character data types are used to manipulate words and freeform
191text. These data types are used to store character. These data types are used for character
192data:
193• CHAR:- The CHAR data type specifies a fixed length character is 1 character and
194maximum allowed is 2000 character.
195• NCHAR:- The NCHAR data types specifies a fixed-length national character set
196character string. The maximum column size allowed is 2000 bytes.
197• NVARCHAR2:- The NVARCHAR2 data type specifies variable-length national
198character string. The maximum column size allowed is 4000 bytes.
199• VARCHAR2:- The VARCHAR2 data type specifies a variable length character string.
200The maximum length of VARCHAR2 data is 4000 bytes.
201Database Applications Lab REVA University
202School of Computing and Information Technology Page 9
203Number Datatypes:- The NUMBER data type is used to store zero, positive and negative
204fixed and floating point numbers with magnitudes
205Floating Point Numbers:- A floating point value either can have a decimal point anywhere
206from the first to the last digit or can omit the decimal point altogether.
207Long Datatype:- LONG columns store variable length variable length character strings
208containing up to 2 gigabytes, or 231 -1 bytes. LONG data type is subject to some restrictions
209which are:
210• A table cannot contain more than one LONG column.
211• LONG columns cannot appear in integrity constraints
212• LONG columns cannot be indexed.
213Also, LONG columns cannot appear in certain parts of SQL statements:
214• WHERE, GROUP BY, or CONNECT BY clause or with the DISTINCT operator in SELECT
215statements
216• UNIQUE clause of a SELECT statement
217• Select list of queries containing GROUP BY clauses
218• Select list of sub queries or queries combined by set operators
219DATE Data type:- The DATE data types is used to store date and time information.
220Operators:- All the normal Arithmetic, Relational, Logical operators are used in SQL.
221SQL Commands:- In order to define schemas, store data, retrieve data and to make
222amendments in schema and data stored in the database different types of commands are
223used which are:
224• Data Definition Language Commands.(DDL)
225• Data Manipulation Language Commands(DML)
226• Transaction Control Commands(TCL)
227• Session Control Commands(SCL)
228• System Control Commands(SCC)
229Data Definition Language (DDL) commands allow you to perform these tasks:
230• Create, Alter, and Drop schema objects(CAD)
231• Grant and Revoke privileges and roles
232• Analyses information on a table, index, or cluster
233• Establish auditing options
234• Add comments to the data dictionary
235Database Applications Lab REVA University
236School of Computing and Information Technology Page 10
237Create Table Command: - It defines each column of the table uniquely. Each column has
238 minimum of three attributes, a name , data type and size.
239Syntax: Create table <table name> (<col1> <datatype>(<size>),<col2> <datatype><size>));
240Ex: Create table emp(empno number(4) primary key, ename char(10));
241Modifying the structure of tables
242a) Add new columns
243Syntax: Alter table <tablename> add(<new col><datatype(size),<new
244col>datatype(size));
245Ex: Alter table emp add(sal number(7,2));
246b) Dropping a column from a table.
247Syntax: Alter table <tablename> drop column <col>;
248Ex: alter table emp drop column sal;
249c) Modifying existing columns.
250Syntax: Alter table <tablename> modify(<col><newdatatype>(<newsize>));
251Ex: Alter table emp modify(ename varchar2(15));
252d)Renaming the tables
253Syntax: Rename <oldtable> to <new table>;
254Ex: rename emp to emp1;
255Truncating the tables
256Syntax: Truncate table <tablename>;
257Ex: trunc table emp1;
258Destroying tables.
259Syntax: Drop table <tablename>;
260Ex: drop table emp;
261Data Manipulation Language (DML) commands query and manipulate data in existing
262schema objects. These commands do not implicitly commit the current transaction.
263Database Applications Lab REVA University
264School of Computing and Information Technology Page 11
265Following are the commands:
2661. Select
2672. Insert
2683. Delete
2694. Update
2705. Lock table
2716. Explain Plan
272Inserting Data into Tables: - once a table is created the most natural thing to do is load this
273table with data to be manipulated later.
274Syntax: insert into <tablename> (<col1>,<col2>) values(<exp>,<exp>);
275Delete operations:-
276a) remove all rows
277Syntax: delete from <tablename>;
278b) removal of a specified row/s
279Syntax: delete from <tablename> where <condition>;
280Updating the contents of a table.
281a) updating all rows
282Syntax: Update <tablename> set <col>=<exp>,<col>=<exp>;
283b) updating selected records.
284Syntax: Update <tablename> set <col>=<exp>,<col>=<exp> where
285<condition>;
286Types of data constrains.
287a) not null constraint at column level.
288Syntax:<col><datatype>(size)not null
289b) unique constraint
290Syntax: Unique constraint at column level.<col><datatype>(size)unique;
291Database Applications Lab REVA University
292School of Computing and Information Technology Page 12
293c) unique constraint at table level:
294Syntax:Create table
295tablename(col=format,col=format,unique(<col1>,<col2>);
296d) primary key constraint at column level
297Syntax:<col><datatype>(size)primary key;
298e) primary key constraint at table level.
299Syntax:Create table tablename(col=format,col=format primary
300key(col1>,<col2>);
301f) foreign key constraint at column level.
302Syntax:<col><datatype>(size>) references <tablename>[<col>];
303g) foreign key constraint at table level.
304Syntax:foreign key(<col>[,<col>])references <tablename>[(<col>,<col>)
305h) check constraint
306i)check constraint constraint at column level.
307Syntax: <col><datatype>(size) check(<logical expression>)
308ii) check constraint constraint at table level.
309Syntax: check(<logical expression>)
310Transaction Control Commands manages change made by Data Manipulation Language
311commands. Following are the commands:
3121. Commit
3132. Rollback
3143. Save point
3154. Set Transaction
316Oracle provides extensive feature in order to safeguard information stored in its tables from
317unauthorized viewing and damage. The rights that allow the user of some or all oracle
318resources on the server are called privileges.
319Database Applications Lab REVA University
320School of Computing and Information Technology Page 13
321Grant privileges using the GRANT statement
322The grant statement provides various types of access to database objects such as tables,
323views and sequences and so on.
324Syntax: GRANT <object privileges> ON <objectname> TO <username>[WITH GRANT
325OPTION];
326Revoke permissions using the REVOKE statement:
327The REVOKE statement is used to deny the Grant given on an object.
328Syntax: REVOKE<object privilege> ON FROM<user name>;
329Aggregate Function
330Aggregate functions return a single value based upon a set of other values. If used among
331many other expressions in the item list of a SELECT statement, the SELECT must have a
332GROUP BY clause. No GROUP BY clause is required if the aggregate function is the only value
333retrieved by the SELECT statement. The supported aggregate functions and their syntax are
334shown in following table.
335Usage of Aggregate Functions Function Name
336Computes the average value of a column by
337the expression
338Avg()
339Counts the rows defined by the expression Count()
340Counts all rows in the specified table or view Count all()
341Finds the minimum value in a column by the
342expression
343Min()
344Finds the maximum value in a column by the
345expression
346Max()
347Computes the sum of column values by the
348expression
349Sum()
350Syntax: Aggregate function name ( [ALL | DISTINCT] expression )
351The aggregate function name may be AVG, COUNT, MAX, MIN, or SUM. The ALL
352clause, which is the default behavior and does not actually need to be specified, evaluates
353all rows when aggregating the value of the function. The DISTINCT clause uses only distinct
354values when evaluating the function.
355Database Applications Lab REVA University
356School of Computing and Information Technology Page 14
357AVG and SUM
358The AVG function computes the average of values in a column or an expression. SUM
359computes the sum. Both functions work with numeric values and ignore NULL values. They
360also can be used to compute the average or sum of all distinct values of a column or
361expression.
362AVG and SUM are supported by Microsoft SQL Server, MySQL, Oracle, and
363PostgreSQL.
364Explanation:- The following query computes average year-to-date sales for each type of
365book:
366SQL> SELECT type, AVG( ytd_sales ) AS "average_ytd_sales"
367 FROM titles GROUP BY type;
368This query returns the sum of year-to-date sales for each type of book:
369SQL> SELECT type, SUM ( ytd_sales )
370 FROM titles GROUP BY type;
371COUNT
372The COUNT function has three variations. COUNT (*) counts all the rows in the target
373table whether they include nulls or not. COUNT (expression) computes the number of rows
374with non-NULL values in a specific column or expression. COUNT (DISTINCT expression)
375computes the number of distinct non-NULL values in a column or expression.
376Explanation :- This query counts all rows in a table:
377SQL>SELECT COUNT (*) FROM publishers;
378The following query finds the number of different countries where publishers are located:
379SQL>SELECT COUNT (DISTINCT country) "Count of Countries" FROM publishers
380MIN and MAX
381MIN (expression) and MAX (expression) find the minimum and maximum value
382(string, date time, or numeric) in a set of rows. DISTINCT or ALL may be used with these
383functions, but they do not affect the result. 
384Database Applications Lab REVA University
385School of Computing and Information Technology Page 15
386Explanation : The following query finds the best and worst sales for any title on record:
387SELECT 'MIN' = MIN(ytd_sales), 'MAX' = MAX(ytd_sales) FROM titles;
388Aggregate functions are used often in the having clause of queries with GROUP BY.
389The following query selects all categories (types) of books that have an average price for all
390books in the category higher than $15.00:
391SQL>SELECT type 'Category', AVG( price ) 'Average Price'
392FROM titles
393GROUP BY type
394HAVING AVG(price) > 15
395CONCATENATE
396SQL99 defines a concatenation operator ( || ), which joins two distinct strings into
397one string value. The CONCATENATE function appends two or more strings together,
398producing a single output string. Oracle support the double-pipe concatenation operator.
399Microsoft SQL Server uses the plus sign (+) concatenation operator.
400SQL> CONCATENATE ('string1' || 'string2')
401Practicing SQL Commands with examples:
402Creating Tables
403In SQL*Plus we can execute any SQL command. One simple type of command creates a
404table (relation). The form is
405 CREATE TABLE <table Name> ( <list of attributes and their types> );
406You may enter text on one line or on several lines. If your command runs over several lines,
407you will be prompted with line numbers until you type the semicolon that ends any
408command. (Warning: An empty line terminates the command but does not execute it; see
409editing commands in the buffer.) An example table-creation command is:
410 CREATE TABLE test ( i int, s char(10) );
411Note that SQL is case insensitive, so CREATE TABLE TEST and create table test are the same.
412This command creates a table named test with two attributes. The first attribute, named i, is
413an integer, and the second, named s, is a character string of length (up to) 10.
414Database Applications Lab REVA University
415School of Computing and Information Technology Page 16
416Exercise 1 : Create a relation Student that suitable for the following instance:
417SID NAME JOB SALARY STREAM START_AT
4181 Ben Kao Associate Professor 7000 E 01-Sep-1995
4192 Eric Lo Teaching Assistant 1000 E 01-Oct-2003
4203 Hammer Lecturer 7000 E 11-Feb-2000
4214 Angela
422Castro
423Program Manager 6000 I 12-Dec-1999
4245 Steven Chu Project Assistant 7000 I 13-Dec-2002
425Note: No need to insert the data yet!
426Inserting Tuples
427Having created a table, we can insert tuples into it. The simplest way to insert is with the
428INSERT command:
429 INSERT INTO <tableName> VALUES( <list of values for attributes, in order> );
430For instance, we can insert the tuple (10, 'hi world') into relation test by
431 INSERT INTO test VALUES(10, 'hi world');
432Exercise 2: Insert the records as stated into Exercise 1 into the student table.
433Trick: Try to insert a record into test with the following SQL:
434 INSERT INTO test VALUES (11, 'ha 'world');
435Updating Tuples
436Tuples can be updated by the UPDATE command:
437 UPDATE <table Name> SET <Attribute>=<Expression/Value>
438 WHERE <Predicate>;
439For example, we can update the tuple (10, 'hi world') in relation test by
440 UPDATE test SET s='bye world' WHERE i=10;
441Database Applications Lab REVA University
442School of Computing and Information Technology Page 17
443Exercise 3: Update the record of 'Eric Lo' in relation Student such that his salary change to
4441234
445Deleting Tuples
446Having insert / update a tuple, we can delete it as well. The simplest way to delete is with
447the DELETE command:
448 DELETE FROM <table Name> [WHERE <condition>];
449<condition> is an optional statement and is used to identify a single record when necessary.
450For example, you can delete the record with i=10 in table test with the the following SQL:
451 DELETE FROM test WHERE i=10;
452Exercise 4: Delete the record of 'Eric Lo' in relation Student.
453Trick: Does that record really deleted successfully? Let's check it out by using SELECT
454command (we will cover it in next section).
455Retrieving Tuples
456We can see the tuples in a relation with the command:
457 SELECT <attributes-separated-by-comma> FROM <tableName>;
458For instance, after the above CREATE, INSERT DELETE and UPDATE statements, the
459command
460 SELECT * FROM test;
461produces the result
462 I S
463 11 ha 'world
464Exercise 5: Select ALL records from relation Student.
465Question: Do data values also case insensitive? i.e., can a student with name "Hammer" be
466retrieved by the following SQL or not?
467 SeLec T name from StudenT where name ='hammer';
468Commit and Rollback
469Database Applications Lab REVA University
470School of Computing and Information Technology Page 18
471An automatic commit occurs under the following circumstance:
472• DDL statement is issued
473• Normal exit from SQL*Plus, without explicitly issuing COMMIT or ROLLBACK
474An automatic rollback occurs under an abnormal termination of SQL*Plus or a system
475failure.
476It provides a good back-door for you to revert the changes you have done on the data.
477Therefore, unless you have issued COMMIT, the changed data would not be visible to any
478other session except your own. Conversely, you can rollback all the changes by issuing the
479ROLLBACK command.
480Exercise 6: Issue the COMMIT command in the SQL*Plus that you have done
481insert/delete/update before, and see if the effect is now visible by the new SQL*Plus?
482Dropping Tables
483To remove a table from your database, execute
484 DROP TABLE <table Name>;
485We suggest you execute
486 DROP TABLE test;
487Caution: Table dropping is a DML statement, which is an action that you cannot rollback.
488Since dropping a table will also delete all data in that table, issue the DROP TABLE command
489with cares.
490Getting Information about Your Database
491The system keeps information about your own database in certain system tables. The most
492important for now is USER_TABLES. You can recall the names of your tables by issuing the
493query:
494 SELECT TABLE_NAME
495 FROM USER_TABLES;
496More information about your tables is available from USER_TABLES. To see all the attributes
497of USER_TABLES, try:
498 SELECT * FROM USER_TABLES;
499It is also possible to recall the attributes of a table once you know its name. Issue the
500command: 
501Database Applications Lab REVA University
502School of Computing and Information Technology Page 19
503 DESCRIBE <tableName>;
504to view the schema of <tableName>;
505Data Types
506Here is part of the data types that are supported by Oracle.
507Data Type Description
508VARCHAR2
509(size)
510Variable-length character data (a maximum size must be specified: Minimum
511size is 1; maximum size is 4000)
512CHAR
513[(size)]
514Fixed-length character data of length size bytes (default and minimum size is 1;
515maximum size is 2000)
516NUMBER
517[(p,s)]
518Number having precision p and scale s (The precision is the total number of
519decimal digits, and the scale is the number of digits to the right of the decimal
520point; the precision can range from 1 to 38 and the scale can range from -84 to
521127)
522DATE
523Date and time values to the nearest second between January 1, 4712 B.C., and
524December 31, 9999 A.D.
525Creating Tables with Keys
526To create a table that declares attribute a to be a primary key:
527 CREATE TABLE <tableName> (..., a <type> PRIMARY KEY, b, ...);
528To create a table that declares the set of attributes (a,b,c) to be a primary key:
529 CREATE TABLE <tableName> (<attrs and their types>, PRIMARY KEY (a,b,c));
530Database Applications Lab REVA University
531School of Computing and Information Technology Page 20
5326. List of Lab Exercises
533SL.
534NO
535Name of the Program PAGE
536NO.
537 I Product - Order System
538In recent years, most of the grocery items are available online; hence
539people are doing online transactions for purchase. There are lot of
540discounts and benefits through the online orders. Since everyone in the
541life is busy with one or other works, such applications will save their
542time.
543These online transaction based applications require many databases to
544be built for storage and transaction management. Design a productorder
545database which can store the details of customers, agents and the
546products. All the details of sold products along with commission from
547different agents across different cities will get stored in this database
548and utilized for transactions.
549Customer (cid, cname, city, discount)
550Agent (aid, aname, city, commission)
551Product (pid, pname, city, quantity, price)
552Orders (ordno, month, cid, aid, pid, qty, amount)
553Queries
554a. Retrieve the customer ids of any product which has been ordered by agent
555"a06".
556b. Retrieve cities in which customers or agents located.
557c. List product ids which have been ordered by agents from the cities
558“Dargeling” or “Srinagar”.
559d. Retrieve customer ids whose discounts are less than the maximum
560discount.
561e. Retrieve product ids ordered by at least two customers.
562f. For each (aid, pid) pair get the sum of the orders aid has placed for pid.
563g. Retrieve product ids and total quantity ordered for each product when the
564total exceeds 1000.
565h. List the names of the customers and agent who placed an order through
566that agent.
567i. Retrieve order numbers placed by customers in "Dargeling" through
568agents in "New Delhi".
569j. Retrieve names of the customers who have the same discount as that of
570any (one) of the customers in "Dargeling" or "Bangalore".
571k. Retrieve customer ids with smaller discounts than every customer from "
572Srinagar”
573l. Retrieve names of the customers who have placed an order through agent
574"a05". (using exists )
575m. Retrieve names of the customers who do not place orders through agent
576"a05". (using not exists)
577n. Retrieve customer ids whose orders placed through all the agents in "New
578Delhi".
579o. Retrieve agent ids either from "New Delhi" or "Srinagar" who place orders
58024
581Database Applications Lab REVA University
582School of Computing and Information Technology Page 21
583for ALL products priced over one dollar.
584p. Retrieve names and ids of the customers and agents along with total dollar
585sales for that pair. Order the result from largest to smallest total sales. Also
586retain only those pairs for which total dollar sales is at least 9000.00.
587q. Increase the percent commission by 50% for all agents in "New York".
588r. Retrieve the total quantity that has been placed for each product.
589II Employee Database System
590The storage of digital data is increasing day by day. Every big / small
591organization started storing their employee details like name, salary, address,
592department under which they are working in their own database. Design a
593company database which can store the details of departments, projects, their
594employee and his / her dependent details of a particular organization
595 Employee (ssn, name, salary, sex, super_ssn, address, dno)
596 Department (dname, dnumber,mgr_ssn)
597 Dept_Loc ( dnumber, dloc)
598 Project (pname, pnumber, plocation, dnum)
599 Works_On (essn, pno, hours)
600 Dependent (essn, depen_name, address, relationship, sex)
601Queries
602a. Retrieve the names of the employees who works on all the projects
603controlled by dept no 3.
604b. Retrieve the names of the employees who gets second highest salary.
605c. Retrieve the names of the employees who have no dependents in
606alphabetical order.
607d. List the names of all employees with at least two dependents.
608e. Retrieve the number of employees and their average salary working in
609each department.
610f. Retrieve the highest salary paid in each department in descending order.
611g. Retrieve the SSN of all employees who work on atleast one of the project
612numbers 1, 2, 3.
613h. Retrieve the number of dependents for an employee named RAM.
614i. Retrieve the names of the managers working in location named xyz who
615has no female dependents.
616j. Retrieve the names of the employees who works in the same department
617as that of RAM.
618k. Retrieve the names of the employees whose salary is greater than the
619salary of all the employees working in department no 3.
620l. Retrieve the names of the employees who work for dept no 3 and have a
621daughter as dependent.
622m. Retrieve the names of the employees who paid highest salary from each
623department.
624n. Retrieve the names of the employees who are paid the same salary as that
625of Anil.
62637
627Database Applications Lab REVA University
628School of Computing and Information Technology Page 22
629o. Retrieve the total the number of employees in the ‘Research’ department.
630p. For each project, retrieve the project number, the project name, and the
631number of employees who work on that project.
632III Car rental agency database systems
633The application that can be used for booking a vehicle online from
634his / her place is very much needed in mobile devices. The main aim of
635this system is to illustrate a database application for booking vehicles
636online. Design a car rental agency database which can store customer
637details, vehicle details like vehicle id, size, transmission and reservation
638details like who had booked from one date to other.
639Customers(cid, firstname, lastname, address)
640Vehicle(vid, mileage, location, vsize, transmission)
641Reservations(cid,vid, start_date, end_date)
642Note :
643->Vehicle.transmission can have two values ‘manual’ and ‘automatic’.
644->Vehicle.vsize can have following values. ‘compact’, ‘mid-size’, ‘fullsize’,
645‘premium’ and ‘luxury’. The default size is compact.
646Queries
647a. Display both the first name and last name in uppercase as “Name of the
648customer “ as column name.
649b. Display vehicles size which is having maximum mileage.
650c. Find location and total mileage of all vehicles specific to each respective
651location.
652d. Find average mileage of vehicles for each location, which has at least
653five vehicles.
654e. Display the customer names whose reservation start date is before Feb
65518th 2016.
656f. Display the vehicles which has been reserved between the dates Nov 5th
6572015 and Jan 16th 2016.
658g. Display the names of the customers whose lastname starts with ‘D’ and
659who has reserved more vehicles than the customer with cid as '101'.
660h. Retrieve the customers who have reserved vehicles from all the locations.
661i. Retrieve the locations that have at least one vehicle with manual
662transmission that has lower mileage than any luxury vehicle at that
663location.
664j. Delete all the reservations for customer whose last name starts with ‘S’.
66552
666Database Applications Lab REVA University
667School of Computing and Information Technology Page 23
6687. Solutions for Exercises
669I. Product - order Database
670Customer (cid, cname, city, discount)
671Agent (aid, aname, city, commission)
672Product (pid, pname, city, quantity, price)
673Orders (ordno, month, cid, aid, pid, qty, amount)
674Aim: Create the tables with the appropriate integrity constraints and Insert around 10
675records
676 in each of the tables
677SQL> Create table Customer ( cid char(4) ,cname varchar(13) not null,city varchar(20),
678 discount real check(discount >= 0.0 and discount <= 15.0),
679 primary key (cid));
680Table created.
681Explanation: The above command will create a new table Customer in database system
682with 4 columns, namely cid, cname, city and discount using not null constraint for cname
683and primary key constraint for cid, discount checking with constraint as discount range+
684greater than zero and less than 15 percent.
685SQL> desc customer;
686OUTPUT:
687Name Null? Type
688CID NOT NULL CHAR(4)
689CNAME NOT NULL VARCHAR2(13)
690CITY VARCHAR2(20)
691DISCOUNT FLOAT(63)
692SQL>Create table agent (aid char(3) ,aname varchar(13) not null, city varchar(20), percent
693 number(6) check(percent >= 0 and percent <= 100),primary key
694(aid));
695Table created.
696Explanation: The above command will create a new table agent in database system with 4
697columns, namely aid, aname, city and percent using not null constraint for aname and
698primary key constraint for aid, percent checking with constraint as percent range greater
699than or equal to zero and less than equal to 100 percent.
700Database Applications Lab REVA University
701School of Computing and Information Technology Page 24
702SQL> desc agent;
703OUTPUT:
704Name Null? Type
705AID NOT NULL CHAR(3)
706ANAME NOT NULL VARCHAR2(13)
707CITY VARCHAR2(20)
708PERCENT NUMBER(6)
709SQL>Create table product ( pid char(3),pname varchar(13) unique not null,city varchar(20),
710 quantity number(10) check(quantity > 0),price real check(price >
711 0.0),primary key (pid));
712Table created.
713Explanation: The above command will create a new table Product in database system with 5
714columns, namely pid, pname, city , Quantity and price using not null constraint for pname
715and primary key constraint for pid , price checking with constraint as range greater than
716zero.
717SQL> desc product;
718OUTPUT:
719Name Null? Type
720PID NOT NULL CHAR(3)
721PNAME NOT NULL VARCHAR2(13)
722CITY VARCHAR2(20)
723QUANTITY NUMBER(10)
724PRICE FLOAT(63)
725SQL>Create table orders ( ordno number(6),month char(3),cid char(4) not null,aid char(3)
726 not null,pid char(3) not null,qty number(6) not null check(qty > 0),
727 ordamount float default 0.0 check(ordamount >= 0.0),primary
728key
729 (ordno),foreign key (cid) references customer, foreign key (aid)
730 references agent,foreign key (pid) references product);
731 Table created.
732Explanation: The above command will create a new table orders in database system with 7
733columns, namely ordno, month, cid ,aid, pid, Qty and ordamount and with foreign key
734create table order (
735 *
736ERROR at line 1:
737ORA-00903: invalid table name
738As order is a reserved word in oracle we cant create a table with name “order” so used orders
739Database Applications Lab REVA University
740School of Computing and Information Technology Page 25
741constraint of cid referring to customer and aid referring agent, pid referring product, ordno
742as primary key and quantity with check constraint with range greater than zero .
743SQL> desc order;
744OUTPUT:
745Name Null? Type
746ORDNO NOT NULL NUMBER(6)
747MONTH CHAR(3)
748CID NOT NULL CHAR(4)
749AID NOT NULL CHAR(3)
750PID NOT NULL CHAR(3)
751QTY NOT NULL NUMBER(6)
752DOLLARS FLOAT(126)
753Insert the data into the table customer, agent , product, orders
754SQL> insert into customer values (’c001’,’Sobhit’,’Darjeling’,10.00);
755 1 row created.
756SQL>insert into customer values (’c002’,’Bhanu’,’Srinagar’,12.00);
7571 row created.
758SQL>insert into customer values (’c003’,’Amar’,’ Srinagar’,8.00);
7591 row created.
760SQL>insert into customer values (’c004’,’Anand’,’Darjeling’,8.00);
7611 row created.
762SQL>insert into customer values (’c005’,’Anand’,’Mumbai’,0.00);
7631 row created.
764NOTE: If an attempt is made to insert the same cid , as it is having primary constraint it
765shows an error.
766SQL> insert into customer values ('c001','Sachin','Darjeling',10.00);
7671 row created.
768SQL>insert into customer values ('c001','Sachin','Darjeling',10.00)*
769ERROR at line 1:
770ORA-00001: unique constraint (DBMS.SYS_C005054) violated
771Database Applications Lab REVA University
772School of Computing and Information Technology Page 26
773SQL> select * from customer;
774OUTPUT:
775CID CNAME CITY DISCOUNT
776c001 Sobhit Darjeling 10
777c002 Bhanu Srinagar 12
778c003 Amar Srinagar 8
779c004 Anand Darjeling 8
780c005 Anand Mumbai 0
781SQL>insert into agent values('a01','Sonu','NewDelhi',6.00);
7821 row created.
783SQL>insert into agent values('a02','John','Agra',6.00);
7841 row created.
785SQL>insert into agent values('a03','Bhargav','Jaipur',7.00);
7861 row created.
787SQL>insert into agent values('a04','Gaurav','NewDelhi',6.00);
7881 row created.
789SQL>insert into agent values('a05','Omkar','Srinagar',5.00);
7901 row created.
791SQL>insert into agent values('a06','Sonu','Darjeling',5.00);
7921 row created.
793SQL> select * from agent;
794OUTPUT:
795AID ANAME CITY PERCENT
796a01 Sonu NewDelhi 6
797a02 John Agra 6
798a03 Bhargav Jaipur 7
799a04 Gaurav NewDelhi 6
800a05 Omkar Srinagar 5
801a06 Sonu Darjeling 5
8026 rows selected.
803Database Applications Lab REVA University
804School of Computing and Information Technology Page 27
805SQL>insert into product values('&PID','&PNAME','&CITY',’&QUANTITY’,’&PRICE’);
806OUTPUT:
807PID PNAME CITY QUANTITY PRICE
808--- ------------- -------------------- ---------- --------------------------
809p01 comb Darjeling 100000 10
810p02 brush Agra 200000 20
811p03 eraser Srinagar 150000 2
812p04 pen Srinagar 100000 15
813p05 pencil Darjeling 170000 3
814p06 folder Darjeling 180000 15
815p07 Highlighter Agra 180000 20
816SQL> insert into orders values ('&ordno','&month','&cid','&aid','&pid',&qty,&ordamount);
817Enter value for ordno: 1011
818Enter value for month: jan
819Enter value for cid: c001
820Enter value for aid: a01
821Enter value for pid: p01
822Enter value for qty: 1000
823Enter value for ordamount: 9400
824old 1: insert into orders values ('&ordno','&month','&cid','&aid','&pid',&qty,&ordamount)
825new 1: insert into orders values ('1011','jan','c001','a01','p01',1000,9400)
8261 row created.
827SQL> /
828Enter value for ordno: 1012
829Enter value for month: jan
830Enter value for cid: c001
831Enter value for aid: a01
832Enter value for pid: p01
833Enter value for qty: 1000
834Enter value for ordamount: 9400
835SQL> select * from orders;
836OUTPUT:
837ORDNO MON CID AID PID QTY ORDAMOUNT
838Database Applications Lab REVA University
839School of Computing and Information Technology Page 28
840 1011 jan c001 a01 p01 1000 9400
841 1012 jan c001 a01 p01 1000 9400
842 1013 jan c002 a03 p03 1000 1860
843 1014 jan c003 a03 p05 1200 3348
844 1015 jan c003 a03 p05 1200 3348
845 1016 jan c005 a01 p01 1000 9400
846 1017 feb c001 a02 p02 400 7520
8471018 feb c001 a03 p04 600 2232
8481019 feb c001 a02 p02 400 7520
849 1020 feb c005 a03 p07 600 11160
8501021 feb c004 a06 p01 1000 9500
8511022 mar c001 a05 p06 400 5700
852 1023 mar c001 a04 p05 500 1410
8531024 mar c005 a06 p01 800 7600
854 1025 Apr c001 a05 p07 800 15200
85515 ows selected.
856a. Retrieve the customer ids of any product which has been ordered by agent "a06".
857SQL> select distinct p.cid from orders o, orders p where p.pid=o.pid and o.aid='a06'
858Explanation: Distinct keyword gives the different values of attribute cid from table Orders
859and product with the join ,pid attribute from product table and pid attribute from order
860table ,whose agent id is ao6.
861OUTPUT:
862CID
863c001
864c004
865c005
866b. Retrieve cities in which customers or agents located.
867SQL> select city from customer
868 union
869 select city from agent;
870Explanation: This query retrieves the city names as the union operator helps to combine
871both the tables customer and agent containing column name city and it won’t allow
872duplicate values.
873OUTPUT:
874CITY
875Agra
876Database Applications Lab REVA University
877School of Computing and Information Technology Page 29
878Darjeling
879Jaipur
880Mumbai
881NewDelhi
882Srinagar
8836 rows selected.
884c. List product ids which have been ordered by agents from the cities “Dargeling” or
885“Srinagar”.
886SQL> select distinct(o.pid) from orders o ,agent a where o.aid=a.aid and
887a.city in( ’Darjeling’,’Srinagar’);
888Explanation: Distict helps to select the distinct values of pid attribute from order table and
889agent table, and the columns aid from tables order and agents with city ‘Darjeeling’ and
890Srinagar using in operator.
891or
892SQL> select distinct(pid) from orders where aid in (select aid from agent where
893city in( ’Darjeling’,’Srinagar’));
894Explanation: Distinct selects the distinct values of pid from orders table and using in
895operator to select the aid attribute from agent table with city names darjeling and Srinagar.
896OUTPUT:
897PID
898p01
899p06
900p07
901d. Retrieve customer ids whose discounts are less than the maximum discount.
902SQL> select cid from customer
903where discount < (select max(discount)
904from customer);
905Explanation: This query gives the customer ids from customer table with condition whose
906discount is less than max discount as max(discount) gives the maximum discount from
907customer table.
908Database Applications Lab REVA University
909School of Computing and Information Technology Page 30
910OUTPUT:
911CID
912c001
913c003
914c004
915c005
916e. Retrieve product ids ordered by at least two customers.
917SQL> select p.pid from product p
918where 2 < = (select count(distinct cid) from orders
919where pid = p.pid);
920Explanation: This Query gives the pids from prouct table ,with condition 2<= count(distinct
921cid) as count gives the no.of distinct cids from orders table and pid column from product
922table .
923OUTPUT:
924PID
925p01
926p05
927p07
928f. For each (aid,pid) pair get the sum of the orders aid has placed for pid
929SQL> select pid, aid, sum(qty) TOTAL
930from orders
931group by pid, aid;
932Explanation: To retrieve pid and aid attribute and the sum operator is used to return the
933total sum of the qty column from orders table using group by function is used to get the
934result in a set of pid and aid attributes.
935OUTPUT:
936PID AID TOTAL
937p01 a01 3000
938p01 a06 1800
939p02 a02 800
940p03 a03 1000
941p04 a03 600
942p05 a03 2400
943p05 a04 500
944p06 a05 400
945p07 a03 600
946p07 a05 800
94710 rows selected.
948Database Applications Lab REVA University
949School of Computing and Information Technology Page 31
950g. Retrieve product ids and total quantity ordered for each product when the total
951exceeds 1000.
952 SQL> select pid, aid, sum(qty) TOTAL
953from orders
954group by pid, aid
955having sum(qty) > 1000;
956Explanation: To retrieve pid and aid attributes and the sum operator is used to return the
957total sum of the qty from orders table using group by function is used to get the result in a
958set of pid and aid attributes and having clause is used instead of where as condition whose
959sum(qty) is greater than 1000.
960OUTPUT:
961PID AID TOTAL
962p01 a01 3000
963p01 a06 1800
964p05 a03 2400
965h. List the names of the customers and agent who placed an order through that agent.
966SQL> select distinct cname, aname
967 from customer, orders, agent
968 where customer.cid = orders.cid and
969 orders.aid = agent.aid;
970Explanation: This query gives distinct values from attributes v=cname,aname from
971customer, orders and agent tables with condition cid from customer table and orders and
972also from aid attribute from orders and agent table.
973OUTPUT:
974CNAME ANAME
975Amar Bhargav
976Anand Bhargav
977Anand Sonu
978Bhanu Bhargav
979Sobhit Bhargav
980Sobhit Gaurav
981Sobhit John
982Sobhit Omkar
983Sobhit Sonu
9846 rows selected.
985Database Applications Lab REVA University
986School of Computing and Information Technology Page 32
987i. Retrieve the order numbers placed by customers in "Dargeling" through agents in
988"NewDelhi".
989SQL>select ordno from orders where cid in (select cid from customer where city =
990 ’Darjeling’) and aid in (select aid from agent where city = ’NewDelhi’);
991
992Explanation: To get ordno from orders table with condition using in operator for
993attribute cid from customer table whose city is darjeling and aid from agent table whose city
994is newdelhi.
995OUTPUT:
996ORDNO
997 1011
998 1012
999 1023
1000j. Retrieve names of the customers who have the same discount as that of any (one) of
1001the
1002 customers in "Dargeling" or "Bangalore".
1003SQL>select cname from customer where discount =any (select discount from customer
1004 where city = ’Darjeling’ or city = ’Bangalore’);
1005Explanation: To get the cname from customer table with condition where any value of
1006discount from customer table whose city is Darjeeling or Bangalore.
1007OUTPUT:
1008CNAME
1009Sobhit
1010Anand
1011Amar
1012k. Retrieve customer ids with smaller discounts than every customer from " Srinagar”
1013SQL>select cid from customer where discount < all (select discount from customer
1014where city = ’Srinagar’);
1015Explanation: To get cid from customer table, with condition whose all discount values is
1016less than the discount of every customer whose city is Srinagar.
1017OUTPUT:
1018CID
1019c005
1020l. Retrieve names of the customers who have placed an order through agent "a05"
1021 (using exists )
1022SQL>select c.cname from customer c where exists (select * from orders o
1023 where c.cid = o.cid and o.aid = ’a05’);
1024Database Applications Lab REVA University
1025School of Computing and Information Technology Page 33
1026Explanation: To get the cname from customer table as exist helps to check the existence of
1027query and selects the complete table from orders with condition cid attribute from
1028customer table and order table whose aid column from order table is a05.
1029 or
1030SQL>select cname from customer where cid in (select cid from orders where aid='a05');
1031OUTPUT:
1032CNAME
1033 Sobhit
1034m. Retrieve names of the customers who do not place orders through agent "a05".
1035(using
1036 not exists)
1037SQL>select cname from customer where cid not in (select cid from orders
1038 where orders.aid = 'a05');
1039Explanation: To retrieve cname from customer table with condition cid from orders table is
1040 not in cid of order table where aid from order table is a05
1041 or
1042SQL>select cname from customer where cid <>any (select cid from orders where
1043 orders.aid = 'a05');
1044OUTPUT:
1045CNAME
1046Bhanu
1047Amar
1048Anand
1049Anand
1050n. Retrieve customer ids whose orders placed through all the agents in "New Delhi".
1051 Get cid values of customers such that (the set of agents from " NewDelhi " through
1052 whom the customer has NOT placed an order) is EMPTY.
1053SQL> select c.cid from customer c
1054where not exists (select * from agent a where a.city = ’NewDelhi’
1055and
1056 not exists (select * from orders o
1057 where o.cid = c.cid and o.aid = a.aid));
1058Explanation: To retrieve cid from customer table ,with condition not exists,as this helps to
1059get the values which are not existed in the agent table whose city is new delhi and also from
1060orders table that do not exist with condition cid column from order table equal to cid
1061column from customer table and also aid columns from order and agent tables.
1062OUTPUT:
1063CID
1064c001
1065Database Applications Lab REVA University
1066School of Computing and Information Technology Page 34
1067o. Retrieve agent ids either from "NewDelhi" or "Srinagar" who place orders for ALL
1068products priced over fifteen rupee.Get aid values of agents from "New York" or
1069"Duluth" such
1070 that (the set of products priced over one dollar that the agent has NOT ordered) is
1071EMPTY.
1072SQL> select a.aid from agent a where (a.city in (’NewDelhi’,’Srinagar’)) and
1073 not exists (select p.pid from product p where p.price > 15.00
1074and
1075 not exists (select * from orders o where o.pid = p.pid and o.aid = a.aid));
1076Explanation:To retrieve aid from agent table whose cities are new delhi and srinagar and
1077using not exist to select the pid from product whose price is greater than 15,and also again
1078using not exist from orders table where pid from order and product table also aid from both
1079tables are equal.
1080OUTPUT:
1081no rows selected
1082So
1083insert into orders values('1026','apr','c005','a05','p02',900,17100);
1084OUTPUT:
1085AID
1086a05
1087p. Retrieve names and ids of the customers and agents along with total sales for that
1088pair.
1089 Order the result from largest to smallest total sales. Also retain only those pairs for
1090which
1091 total rupee sales is at least 9000.00.
1092SQL> select c.cname, c.cid, a.aname, a.aid, sum(o.ordamount)
1093from customer c, orders o, agent a
1094where c.cid = o.cid and o.aid = a.aid
1095group by c.cname, c.cid, a.aname, a.aid
1096having sum(o.ordamount) >= 9000.00
1097order by 5 desc;
1098Explanation:To retrieve cname,cid,from customer table and aname and aid from agent
1099table,with sum function for order amount from order table ,customer and agent table with
1100condition cid from customer and order tables and aid from order and agent tables are equal
1101and to get the result in one set group by is used for cname,cid of customer table and aid
1102,aname from agent table with sum of ordramount of order table is >= 9000 in descending
1103order as result.
1104Database Applications Lab REVA University
1105School of Computing and Information Technology Page 35
1106OUTPUT:
1107CNAME CID ANAME AID SUM(O.ORDAMOUNT)
1108Sobhit c001 Omkar a05 20900
1109Sobhit c001 Sonu a01 18800
1110Sobhit c001 John a02 15040
1111Anand c005 Bhargav a03 11160
1112Anand c004 Sonu a06 9500
1113Anand c005 Sonu a01 9400
11146 rows selected.
1115q.Increase the percent commission by 50% for all agents in "NewDelhi".
1116SQL> update agent
1117set percent = 1.5 * percent
1118where city = ’NewDelhi;
1119Explanation:To update agent table, percentage value is set to 1.5*percent to get 50%
1120whose city is NewDelhi.
1121r. Retrieve the total quantity that has been placed for each product
1122SQL> select pid, sum(qty) TOTAL
1123from orders
1124group by pid;
1125Explanation: To get pid with total sum of qty from orders table using group by to get the
1126result in one set.
1127OUTPUT:
1128PID TOTAL
1129p01 4800
1130p02 800
1131p03 1000
1132p04 600
1133p05 2900
1134p06 400
1135p07 1400
11367 rows selected.
1137II. Company Database
1138Database Applications Lab REVA University
1139School of Computing and Information Technology Page 36
1140Designing a company database which can store department, project, employee and his
1141dependent details of a particular organization.
1142 Employee (ssn, name, salary, sex, super_ssn, address, dno)
1143Department (dname, dnumber)
1144Dept_Loc ( dnumber, dloc, mgrssn)
1145Project (pname, pnumber, plocation, dnum)
1146Works_On (essn, pno, hours)
1147Dependent (essn, depen_name, address, relationship, sex)
1148
1149Aim: Create the tables with the appropriate integrity constraints and Insert around 10
1150records
1151 in each of the tables
1152NOTE: Department table has a column mgr_ssn which is a foreign key referring to ssn
1153column
1154 of an employee table And employee table has a column dno which is a foreign key
1155 referring to dnumber of department table.So it is interlinked and deadlock
1156appears.
1157step i create table department with attributes dno, dname (without mgr_ssn column)
1158step ii insert data into department
1159step iii create table employee and insert data ( without super_ssn column)
1160step iv insert data into employee table
1161step v add new column super_ssn into employee table and update data to column
1162super_ssn
1163step vi add new column mgrssn into department referring to employee table
1164stpe vii insert data of mgrssn in department table
1165SQL>Create table Department (dname varchar(15), unique not null, dnumber int ,
1166 Primary key (dnumber));
1167Table Created.
1168SQL> Create table Employee(ssn char(9), name varchar(15) not null, salary decimal(10,2),
1169 sex char, address varchar(30),dno int not null, primary key(ssn),
1170 foreign key(dno) references Department(dnumber));
1171Table Created.
1172SQL>Create table Dept_Location(dnumber int not null, dlocation varchar(15) not null,
1173 primary key(dnumber, dlocation), foreign key(dnumber)
1174 references Department(dnumber) );
1175Table Created.
1176SQL>Create table Projet( pname varchar(15) not null, pnumber varchar(5) not null,
1177 plocation varchar(15),dnum int not null, primary key (pnumber),
1178 unique(pname),foreign key (dnum) references
1179Department(dnumber)); 
1180Database Applications Lab REVA University
1181School of Computing and Information Technology Page 37
1182Table Created.
1183SQL>Create table workson(essn char(9)not null, pno varchar(5) not null, hours decimal(3,1)
1184 not null , primary key(essn, pno),foreign key (essn) references
1185 employee(ssn), foreign key (pno) references project(pnumber));
1186Table Created.
1187SQL>Create table Dependent (essn char(9)not null, dependent_name varchar(15)not null,
1188 sex CHAR,relationship varchar(8),primary key (essn),
1189 dependent_name),foreign key(essn) references
1190Employee(ssn));
1191Table Created.
1192SQL> desc Department;
1193OUTPUT:
1194Name Null? Type
1195DNAME NOT NULL VARCHAR2(15)
1196DNUMBER NOT NULL NUMBER(38)
1197SQL> desc Employee;
1198OUTPUT :
1199Name Null? Type
1200SSN NOT NULL CHAR(9)
1201NAME NOT NULL VARCHAR2(15)
1202SALARY NUMBER(10,2)
1203SEX CHAR(1)
1204ADDRESS VARCHAR2(30)
1205DNO NOT NULL NUMBER(38)
1206SQL> desc employee;
1207OUTPUT :
1208Name Null? Type
1209SSN NOT NULL CHAR(9)
1210NAME NOT NULL VARCHAR2(15)
1211SALARY NUMBER(10,2)
1212SEX CHAR(1)
1213ADDRESS VARCHAR2(30)
1214DNO NOT NULL NUMBER(38)
1215SUPER_SSN CHAR(9)
1216Database Applications Lab REVA University
1217School of Computing and Information Technology Page 38
1218SQL> desc Dept_Location;
1219OUTPUT:
1220Name Null? Type
1221DNUMBER NOT NULL NUMBER(38)
1222DLOCATION NOT NULL VARCHAR2(15)
1223SQL> desc Project;
1224OUTPUT:
1225Name Null? Type
1226PNAME NOT NULL VARCHAR2(15)
1227PNUMBER NOT NULL VARCHAR2(5)
1228PLOCATION VARCHAR2(15)
1229DNUM NOT NULL NUMBER(38)
1230SQL> desc Workson;
1231OUTPUT:
1232Name Null? Type
1233 ESSN NOT NULL CHAR(9)
1234 PNO NOT NULL VARCHAR2(5)
1235 HOURS NOT NULL NUMBER(3,1)
1236SQL> desc Dependent;
1237OUTPUT:
1238Name Null? Type
1239ESSN NOT NULL CHAR(9)
1240DEPENDENT_NAME NOT NULL VARCHAR2(15)
1241SEX CHAR(1)
1242RELATIONSHIP VARCHAR2(8)
1243SQL> Insert into Department values (‘Research’,1);
12441 row created.
1245SQL> Insert into Department values (‘HR’,2);
12461 row created.
1247SQL> Insert into Department values (‘Development’,3);
12481 row created.
1249SQL> Insert into Department values (‘Testing’,4);
12501 row created.
1251SQL> select * from Department;
1252Database Applications Lab REVA University
1253School of Computing and Information Technology Page 39
1254OUTPUT:
1255DNAME DNUMBER
1256Research 1
1257HR 2
1258Development 3
1259Testing 4
1260SQL>Insert into Employee values('emp001','Ram',30000,'M','RT Nagar, Blore',3);
12611 row created.
1262SQL>Insert into Employee values('emp002','Sudha',75000,'F','Hebbal, Blore',2);
12631 row created.
1264SQL>Insert into Employee values('emp003','Ravi’,20000,'M','Hebbal, Blore',4);
12651 row created.
1266SQL>Insert into Employee values('emp004','Rohan',80000,'M','RT Nagar, Mysore',1);
12671 row created.
1268SQL>Insert into Employee values('emp005','Amar',35000,'M','MG Road, Mysore',3);
12691 row created.
1270SQL>Insert into Employee values('emp006','Anil',45000,'M','MG Road, Noida’,3);
12711 row created.
1272SQL>Insert into Employee values('emp007','Tanya',35000,'F','Yelahanka, Blore',3);
12731 row created.
1274SQL>Insert into Employee values('emp008','Kavita',50000,'F','Baglur, Blore',1);
12751 row created.
1276SQL>Insert into Employee values('emp009','John',45000,'M','RT Nagar, Blore',4);
12771 row created.
1278SQL> select * from employee;
1279OUTPUT:
1280SSN NAME SALARY SEX ADDRESS DNO
1281emp001 Ram 30000 M RT Nagar, Blore 3
1282emp002 Sudha 75000 F Hebbal, Blore 2
1283emp003 Ravi 20000 M Hebbal, Blore 4
1284emp004 Rohan 80000 M RT Nagar, Mysore 1
1285emp005 Amar 35000 M MG Road, Mysore 3
1286emp006 Anil 45000 M MG Road, Noida 3
1287emp007 Tanya 35000 F Yelahanka, Blore 3
1288emp008 Kavita 50000 F Baglur, Blore 1
1289emp009 John 45000 M RT Nagar, Blore 4
1290SQL>alter table employee add super_ssn char(9) references employee(ssn);
1291Table altered.
1292Database Applications Lab REVA University
1293School of Computing and Information Technology Page 40
1294SQL>update employee set super_ssn=’emp006’ where ssn=’emp001’;
12951 row updated.
1296SQL>update employee set super_ssn=’emp008’ where ssn=’emp003’;
12971 row updated.
1298SQL>update employee set super_ssn=’emp002’ where ssn=’emp005’;
12991 row updated.
1300SQL>update employee set super_ssn=’emp008’ where ssn=’emp006’;
13011 row updated.
1302SQL>update employee set super_ssn=’emp008’ where ssn=’emp007’;
13031 row updated.
1304SQL>update employee set super_ssn=’emp004’ where ssn=’emp008’;
13051 row updated.
1306SQL>update employee set super_ssn=’emp008’ where ssn=’emp009’;
13071 row updated.
1308SQL> select * from employee;
1309OUTPUT:
1310SSN NAME SALARY S ADDRESS DNO
1311SUPER_SSN
1312emp001 Ram 30000 M RT Nagar, Blore 3 emp006
1313emp002 Sudha 75000 F Hebbal, Blore 2
1314emp003 Ravi 20000 M Hebbal, Blore 4 emp008
1315emp004 Rohan 80000 M RT Nagar, Mysore 1
1316emp005 Amar 35000 M MG Road, Mysore 3 emp002
1317emp006 Anil 45000 M MG Road, Noida 3 emp008
1318emp007 Tanya 35000 F Yelahanka, Blore 3 emp008
1319emp008 Kavita 50000 F Baglur, Blore 1 emp004
1320emp009 John 45000 M RT Nagar, Blore 4 emp008
1321SQL>alter table department add mgr_ssn char(9) references employee(ssn);
1322SQL> desc department;
1323OUTPUT:
1324Name Null? Type
1325DNAME NOT NULL VARCHAR2(15)
1326DNUMBER NOT NULL NUMBER(38)
1327MGR_SSN CHAR(9)
1328Database Applications Lab REVA University
1329School of Computing and Information Technology Page 41
1330SQL> select * from department;
1331OUTPUT:
1332DNAME DNUMBER MGR_SSN
1333Research 1
1334HR 2
1335Development 3
1336Testing 4
1337SQL>update department set mgr_ssn=’emp004’ where dnumber=1;
1338 1 row updated
1339SQL>update department set mgr_ssn=’emp002’ where dnumber=2;
13401 row updated
1341SQL>update department set mgr_ssn=’emp006’ where dnumber=3;
13421 row updated
1343SQL>update department set mgr_ssn=’emp009’ where dnumber=4;
13441 row updated
1345SQL> select * from department;
1346OUTPUT:
1347DNAME DNUMBER MGR_SSN
1348Research 1 emp004
1349HR 2 emp002
1350Development 3 emp006
1351Testing 4 emp009
1352SQL>Insert into Dept_Location values(1,’Blore’);
13531 row created.
1354SQL>Insert into Dept_Location values(2,’Blore’);
13551 row created.
1356SQL>Insert into Dept_Location values(3,’Blore’);
13571 row created.
1358SQL>Insert into Dept_Location values(3,’Mysore’);
13591 row created.
1360SQL>Insert into Dept_Location values(4,’Noida’);
13611 row created.
1362SQL>Insert into Dept_Location values(4,’Blore’);
13631 row created.
1364Database Applications Lab REVA University
1365School of Computing and Information Technology Page 42
1366SQL> select * from Dept_Location;
1367OUTPUT:
1368DNUMBER DLOCATION
13691 Blore
1370 2 Blore
1371 3 Blore
1372 3 Mysore
1373 4 Noida
1374 4 Blore
1375SQL>Insert into project values(‘Banking’,’p01’,’Blore’,3);
13761 row created.
1377SQL>Insert into project values(‘Android App’,’p02’,’Mysore’,3);
13781 row created.
1379SQL>Insert into project values(‘WSN’,’p03’,’Blore’,4);
13801 row created.
1381SQL>Insert into project values(‘Robotics’,’p04’,’Noida’,4);
13821 row created.
1383SQL>Insert into project values(‘Smart Vehicle’,’p05’,’Blore’,3);
13841 row created.
1385SQL> select * from project;
1386OUTPUT:
1387PNAME PNUMBER PLOCATION DNUM
1388Banking p01 Blore 3
1389Android App p02 Mysore 3
1390WSN p03 Blore 4
1391Robotics p04 Noida 4
1392Smart Vehicle p05 Blore 3
1393SQL>Insert into workson values(‘emp001’,’p01’,9);
13941 row created.
1395SQL>Insert into workson values(‘emp003’,’p01’,10);
13961 row created.
1397SQL>Insert into workson values(‘emp001’,’p02’,7);
13981 row created.
1399SQL>Insert into workson values(‘emp005’,’p03’,18);
14001 row created.
1401SQL>Insert into workson values(‘emp003’,’p02’,14);
14021 row created.
1403Database Applications Lab REVA University
1404School of Computing and Information Technology Page 43
1405SQL>Insert into workson values(‘emp004’,’p05’,12);
14061 row created.
1407SQL>Insert into workson values(‘emp007’,’p04’,14);
14081 row created.
1409SQL>Insert into workson values(‘emp001’,’p05’,12);
14101 row created.
1411SQL> select * from workson;
1412OUTPUT:
1413ESSN PNO HOURS
1414emp001 p01 14
1415emp003 p01 10
1416emp001 p02 7
1417emp005 p03 18
1418emp003 p02 14
1419emp004 p05 12
1420emp007 p04 14
1421emp001 p05 12
1422SQL> desc Dependent;
1423OUTPUT:
1424Name Null? Type
1425ESSN NOT NULL CHAR(9)
1426DEPENDENT_NAME NOT NULL VARCHAR2(15)
1427SEX CHAR(1)
1428RELATIONSHIP VARCHAR2(8)
1429SQL>Insert into Dependent values(‘emp001’,’Raghu’,’M’,’son’);
14301 row created.
1431SQL>Insert into Dependent values(‘emp004’,’Reshma’,’F’,’wife’);
14321 row created.
1433SQL>Insert into Dependent values(‘emp007’,’Bindu’,’F’,’daughter’);
14341 row created.
1435SQL>Insert into Dependent values(‘emp009’,’Shaan’,’M’,’son’);
14361 row created.
1437SQL>insert into dependent values('emp009','Shamir','M','son');
14381 row created.
1439Database Applications Lab REVA University
1440School of Computing and Information Technology Page 44
1441SQL> select * from dependent;
1442OUTPUT:
1443ESSN DEPENDENT_NAME SEX RELATION
1444emp001 Raghu M son
1445emp004 Reshma F wife
1446emp007 Bindu F daughter
1447emp009 Shaan M son
1448emp009 Shamir M son
1449a. Retrieve the names of the employees who works on all the projects controlled by dept
1450no 3
1451SQL>select name from employee
1452 where not exists ((select pnumber from project where dnum = 3)
1453 MINUS
1454 (select pno from workson where essn = ssn));
1455Explanation :This Query gives the name of the employee using not exists with result of
1456minus operator whose pnumber from project table with condition dnum as 3 and pno from
1457workson table where essn=ssn.
1458OUTPUT:
1459NAME
1460Ram
1461b. Retrieve the names of the employees who gets second highest salary
1462SQL> select name from employee where salary in (select max(salary) from employee where
1463salary not in (select max(salary) from employee));
1464or
1465SQL> select name from employee where salary in (select max(salary) from employee where
1466salary < (select max(salary) from employee));
1467Explanation: This Query gives the name of the employee using max function for salary
1468column from employee table, whose sal is not in max(salary) or by using < operator .
1469OUTPUT:
1470NAME
1471Sudha
1472Database Applications Lab REVA University
1473School of Computing and Information Technology Page 45
1474C.Retrieve the names of the employees who have no dependents in alphabetical order.
1475SQL>select name
1476from employee e
1477where not exists (select * from dependent where essn=e.ssn) order by name;
1478or
1479SQL>select name from employee where ssn not in (select essn from dependent) order by
1480name;
1481Explanation: This query gives the name of employee using not exists in dependent with
1482condition essn=e.ssn using order by name.
1483or
1484By using not in and ssn column from dependent table using order by .
1485OUTPUT:
1486NAME
1487Anil
1488Sudha
1489Ravi
1490Amar
1491Kavita
1492d.List the names of all employees who have at least two dependents
1493SQL> Select name from employee
1494where ( SELECT COUNT (*) FROM DEPENDENT WHERE Ssn =Essn )>=2;
1495Explanation: This query gives the names of the employees,using count function as it gives
1496the count value of the selected table with condition ssn=Essn and >=2.
1497OUTPUT :
1498NAME
1499John
1500e. Retrieve the number of employees and their average salary working in each
1501department.
1502SQL>select dno, count (*),avg(salary) from employee group by dno;
1503Explanation: This Query gives dno,count and avg sal from employee table using group by
1504dno .
1505Database Applications Lab REVA University
1506School of Computing and Information Technology Page 46
1507OUTPUT :
1508DNO COUNT(*) AVG(SALARY)
1509 1 2 65000
1510 2 1 75000
1511 3 4 36250
15124 2 32500
1513f. Retrieve the highest salary paid in each department in descending order.
1514SQL>select dno,max(salary) from employee group by dno order by max(salary) desc;
1515Explanation: This Query gives the dno and Max (salary) Using Group by and Oder by
1516functions
1517 with conditions group by dno and order by max(salary) as desc.
1518OUTPUT :
1519 DNO MAX(SALARY)
1520 1 80000
1521 2 75000
1522 3 45000
1523 4 45000
1524g. Retrieve the SSN of all employees who work on atleast one of the project numbers 1, 2,
15253
1526SQL>select distinct(essn) from workson where pno in (‘p01’,’p02’,’p03’);
1527or
1528SQL>select distinct(essn) from workson where pno=‘p01’or pno=’p02’ or pno=’p03’;
1529Explanation :This Query gives the Essn of employee from workson table using distinct with
1530 condition pno in (p01,p02,p03) or pno=p01 or p02,p03
1531OUTPUT :
1532ESSN
1533emp001
1534emp003
1535emp005
1536Database Applications Lab REVA University
1537School of Computing and Information Technology Page 47
1538h. Retrieve the number of dependents for an employee named RAM.
1539SQL>select count(*) from dependent where essn=(select ssn from employee where
1540name='Ram');
1541or
1542SQL>select count(*) from employee e, dependent d where d.essn=e.ssn and e.name='Ram';
1543Explanation :This Query gives the no .of dependents using count function for dependent
1544table with condition whose essn is equal to ssn of employee table whose ename is ram .
1545Or
1546Explanation: By using count for employee table and dependent with condition essn of
1547dependent table = ssn of employee table and whose name from employee table is ram.
1548OUTPUT :
1549COUNT(*)
1550 1
1551i. Retrieve the names of the managers working in location named xyz who has no female
1552dependents.
1553SQL>select name from employee where ssn in
1554(select essn from dependent where sex!='F' and essn in(
1555(select mgr_ssn from department where dnumber in
1556 (select dnumber from dept_location where dlocation='Blore'))));
1557or
1558SQL>select distinct(name) from employee e , department d, dept_location l, dependent de
1559where e.ssn= de.essn and de.sex!='F' and de.essn=d.mgr_ssn and
1560d.dnumber=l.dnumber and l.dlocation='Blore';
1561Explanation: This query gives the name of manger from employee table by using IN
1562operator,
1563whose ssn from dependent table sex is not ‘F’ and mgr_ssn from department,whose
1564dnumber from dep_location is Blore.
1565Or
1566Explanation: By using distinct for name from employee table ,department ,dependent and
1567location tables whose ssn from employee =essn from dependent table and sex is not ‘F’ in
1568dependent and essn from dependent =mgr from department mgr_ssn and dnumber from
1569d=dhumber from l;and dlocation from l=’Blore’.
1570OUTPUT :
1571NAME
1572John
1573Database Applications Lab REVA University
1574School of Computing and Information Technology Page 48
1575j. Retrieve the names of the employees who works in the same department as that of
1576RAM
1577SQL>select name from employee where dno=
1578(select dno from employee where name='Ram') and name!='Ram';
1579Explanation: This gives the name from employee using condition get dno whose name is
1580ram
1581 from emp table and name whose name is not ram.
1582OUTPUT :
1583NAME
1584Amar
1585Anil
1586Tanya
1587k. Retrieve the name of the employees whose salary is greater than the salary of all the
1588 employees working in department 3.
1589SQL>SELECT name, salary FROM EMPLOYEE
1590WHERE Salary> ALL ( SELECT Salary FROM EMPLOYEE WHERE Dno=3 );
1591Explanation: To get the name, salary of employee from emp table using condition
1592 whose salary is > All employees and whose dno=3.
1593OUTPUT :
1594NAME SALARY
1595Sudha 75000
1596Rohan 80000
1597Kavita 50000
1598l. Retrieve the names of the employees who work for dept no 3 and have a daughter as
1599 dependent.
1600SQL>select name from employee e , dependent d
1601 where e.ssn=d.essn and d.relationship='daughter' and e.dno=3;
1602Explanation: This Query gives the name using conditions ssn fromemp=essn from
1603dependent
1604 and relationship from depend=daughter and dno from emp=3.
1605OUTPUT :
1606NAME
1607Tanya
1608m. Retrieve the employee name who paid highest salary from each department
1609SQL>select name from employee where salary in
1610(select max(salary) from employee group by dno);
1611Database Applications Lab REVA University
1612School of Computing and Information Technology Page 49
1613Explanation: To get the name from emp using condition whose sal in emp using max
1614and groupby dno.
1615OUTPUT :
1616NAME
1617Rohan
1618Sudha
1619John
1620Anil
1621n. Retrieve the names of the employees who are paid the same salary as that of Anil
1622select name from employee where salary in (select salary from employee where
1623name='Anil') and name!=’Anil’;
1624or
1625select name from employee where salary = (select salary from employee where name='Anil')
1626and name!=’Anil’;
1627Explanation This gives the name of employee using In operator and condition sal frm emp
1628table whose name is Anil and name frm emp whose name is not anil.
1629OUTPUT :
1630NAME
1631John
1632o. Retrieve the total the number of employees in the ‘Research’ department.
1633SQL>select count (*) from Employee, department where dno =dnumber and
1634 dname=‘Research’;
1635Explanation: To get the total ,we use count function along with condition whose dno is
1636 dnumber and dname is research.
1637OUTPUT :
1638COUNT(*)
1639 2
1640Database Applications Lab REVA University
1641School of Computing and Information Technology Page 50
1642p. For each project, retrieve the project number, the project name, and the number of
1643 employees who work on that project.
1644SQL>SELECT Pnumber, Pname, COUNT (*)
1645FROM Project, Workson
1646WHERE Pnumber=Pno GROUP BY Pnumber, Pname;
1647Explanation: This query gives the pnumb ,pname and total number from project and
1648workson
1649 tables using condition whose pnumber is pno and group by pnumber and
1650pname.
1651OUTPUT :
1652PNUMB PNAME COUNT(*)
1653p01 Banking 2
1654p02 Android App 2
1655p03 WSN 1
1656p04 Robotics 1
1657p05 Smart Vehicle 2
1658Database Applications Lab REVA University
1659School of Computing and Information Technology Page 51
1660III. Car rental agency database
1661Design a car rental agentcy database which can store customer details, vehicle details like
1662vehicle id, size, transmission and reservation details like who had booked from one date
1663to other.
1664Customers(cid, firstname, lastname, address)
1665Vehicle(vid, mileage, location, size, transmission)
1666Reservations(cid,vid, start_date, end_date)
1667Note :
1668->Vehicle.transmission can have two values ‘manual’ and ‘automatic’.
1669->Vehicle.size can have following values. ‘compact’, ‘mid-size’, ‘full-size’, ‘premium’ and
1670 ‘luxury’. The default size is compact.
1671Aim: Create the tables with the appropriate integrity constraints and Insert around 10
1672records
1673 in each of the tables
1674SQL> create table carcustomer (cid varchar(5) ,first_name varchar(20) not null,last_name
1675 varchar(15) not null,address varchar(30),Primary key(cid) );
1676Table Created
1677or
1678SQL>create table carcustomer ( cid varchar(5) PRIMARY KEY,first_name varchar(20) not null,
1679 last_name varchar(15) not null,address varchar(30) );
1680Table Created
1681SQL> create table vehicle ( vid varchar(5) primary key,mileage number(7,2),location
1682 varchar(20),vsize varchar(30) check (vsize in('compact','mid-size','full-
1683 size','premium','luxury')),transmission varchar(10) check (transmission
1684 in('manual','automatic')) );
1685Table Created
1686SQL> create table reservation ( cid varchar(5) references carcustomer(cid),vid varchar(5)
1687references vehicle(vid),start_date date,end_date date,primary key(cid,vid) );
1688Table Created
1689Database Applications Lab REVA University
1690School of Computing and Information Technology Page 52
1691SQL> desc carcustomer;
1692OUTPUT:
1693Name Null? Type
1694CID NOT NULL VARCHAR2(5)
1695FIRST_NAME NOT NULL VARCHAR2(20)
1696LAST_NAME NOT NULL VARCHAR2(15)
1697ADDRESS VARCHAR2(30)
1698SQL> desc vehicle;
1699OUTPUT:
1700Name Null? Type
1701 VID NOT NULL VARCHAR2(5)
1702MILEAGE NUMBER(7,2)
1703LOCATION VARCHAR2(20)
1704VSIZE VARCHAR2(30)
1705TRANSMISSION VARCHAR2(10)
1706SQL> desc reservation;
1707OUTPUT:
1708Name Null? Type
1709VID NOT NULL VARCHAR2(5)
1710START_DATE DATE
1711END_DATE DATE
1712SQL>insert into carcustomer values ('101','Karan','P','Malleswaram,Blore');
1713SQL>insert into carcustomer values ('102','Bhuvan','M’,'Vailakavali, Blore');
1714SQL>insert into carcustomer values ('103','Darshan',’D’,'Devanahalli,Devan');
1715SQL>insert into carcustomer values ('104','Vishal','D','Hebbal, Blore');
1716SQL>insert into carcustomer values ('105','Sagar','S','Kalyannagar, Blore');
1717Database Applications Lab REVA University
1718School of Computing and Information Technology Page 53
1719SQL> select * from carcustomer;
1720OUTPUT:
1721CID FIRST_NAME LAST_NAME ADDRESS
1722101 Karan P Malleswaram,Blore
1723102 Bhuvan M Vailakavali, Blore
1724103 Darshan D Devanahalli,Devan
1725104 Vishal D Hebbal, Blore
1726105 Sagar S Kalyannagar, Blore
1727SQL> insert into vehicle values ('V-101','70',' Blore ','compact','automatic');
1728SQL>insert into vehicle values ('V-102','50','Surat','compact','automatic');
1729SQL>insert into vehicle values ('V-103','10',' Blore ','mid-size','manual');
1730SQL>insert into vehicle values ('V-104','30',' Blore ','mid-size','automatic');
1731SQL>insert into vehicle values ('V-105','15',' Vailakavali ','full-size','automatic');
1732SQL>insert into vehicle values ('V-106','20',' Blore ','luxury','automatic');
1733SQL>insert into vehicle values ('V-107','50',' Blore ','luxury','manual');
1734SQL> select * from vehicle;
1735VID MILEAGE LOCATION VSIZE TRANSMISSION
1736V-101 70 Blore compact automatic
1737V-102 50 Surat compact automatic
1738V-103 10 Blore mid-size manual
1739V-104 30 Blore mid-size automatic
1740V-105 15 Vailakavali full-size automatic
1741V-106 20 Blore luxury automatic
1742V-107 50 Blore luxury manual
17437 rows selected.
1744Database Applications Lab REVA University
1745School of Computing and Information Technology Page 54
1746SQL>insert into reservation values ('101','V-101','15-FEB-2009','18-FEB-2009');
1747SQL>insert into reservation values ('102','V-102','10-JAN-2011','15-JAN-2011');
1748SQL>insert into reservation values ('103','V-103','20-FEB-2013','24-FEB-2013');
1749SQL>insert into reservation values ('105','V-105','12-MAR-2016','10-JUN-2016');
1750SQL> select * from reservation;
1751OUTPUT:
1752CID VID START_DAT END_DATE
1753101 V-101 15-FEB-09 18-FEB-09
1754102 V-102 10-JAN-11 15-JAN-11
1755103 V-103 20-FEB-13 24-FEB-13
1756105 V-105 12-MAR-16 10-JUN-16
1757a. Display both the firstname and last name in uppercase as Name of the customer as
1758column name.
1759SQL> select upper(first_name) || ' '||upper(last_name) as "Name of the customers" from
1760 carcustomer;
1761OUTPUT:
1762Name of the customers
1763KARAN P
1764BHUVAN M
1765DARSHAN D
1766VISHAL D
1767SAGAR S
1768Explanation: This query concatenates the first name and last name in uppercase whose first
1769name is "Vishal".
1770b. Display vehicles size which is having maximum mileage.
1771SQL> select vsize from vehicle where mileage = (select max(mileage) from vehicle);
1772OUTPUT:
1773VSIZE
1774Compact
1775Explanation: This query displays the vehicle size which has the maximum mileage.
1776c. Find location and total mileage of all vehicles specific to each respective location.
1777SQL> select location, sum(mileage) from vehicle group by location;
1778Database Applications Lab REVA University
1779School of Computing and Information Technology Page 55
1780OUTPUT:
1781LOCATION SUM(MILEAGE)
1782Blore 180
1783Vailakavali 15
1784Surat 50
1785Explanation: This query displays the location and total mileage of all vehicles specific to
1786each respective location using aggregate function Sum () and group by clause.
1787d. Find average mileage of vehicles for each location, which has at least five vehicles.
1788SQL> select avg(mileage),count(vid),location from vehicle
1789 group by location having count(vid) >= 5;
1790OUTPUT:
1791AVG(MILEAGE) COUNT(VID) LOCATION
1792 36 5 Blore
1793Explanation: This query displays the average mileage of vehicles for each location in which
1794the location has at least five vehicles using Count and Avg aggregate functions.
1795e. Display the customer names whose reservation start date is before 18 Feb 2012.
1796SQL> SELECT c.first_name,c.last_name FROM RESERVATION r, carcustomer c
1797 where c.cid=r.cid and r.start_date< '18-FEB-2012';
1798OUTPUT:
1799FIRST_NAME LAST_NAME
1800Karan P
1801Bhuvan M
1802Explanation: This query display the customer names whose reservation start date is before
180318 Feb 2012 by using the inner join between tables Customer and Reservation.
1804Database Applications Lab REVA University
1805School of Computing and Information Technology Page 56
1806f. Display the vehicles which has been reserved between the dates Nov 5th 2015 and Jan
180716th 2016
1808SQL> select vid from RESERVATION
1809 WHERE start_date >= ‘5-JAN-2011’ AND end_date <= ‘1-JAN-2014’;
1810OUTPUT:
1811VID
1812V-102
1813V-103
1814Explanation: This query display the vehicles which has been reserved between the dates
1815Nov 5th 2015 and Jan 16th 2016 using Reservation table and where clause.
1816g. Display the names of the customers whose lastname starts with ‘D’ and who has
1817reserved more vehicles than the customer with CID as 101.
1818SQL> select c.first_name from carcustomer c
1819where c.last_name like ‘D%’ and c.cid in( select cid from reservation
1820group by cid having count(*) >(select count(*) from RESERVATION where cid
1821='101'));
1822OUTPUT:
1823No rows selected
1824cid’s 103 &104 are having lastname as D,
1825101 is having one record in reservation
1826So either 103 or 104 should have atleast 2 records
1827So I can insert minimum 1 record for 103 or 2 records for 104
1828SQL> insert into reservation values ('103','V-104','15-OCT-2014','24-OCT-2014');
1829OUTPUT:
1830FIRST_NAME
1831Darshan
1832Database Applications Lab REVA University
1833School of Computing and Information Technology Page 57
1834h. Retrieve the customers who have reserved vehicles from all the locations.
1835SQL> select c.cid from carcustomer c
1836where not exists
1837((select distinct(location) from vehicle)
1838 minus
1839(select v.location from vehicle v, reservation r where v.vid=r.vid and c.cid=r.cid));
1840OUTPUT:
1841No rows selected
1842NOTE :In reservation table we have 5records in which
1843 101, 102, 105 reserved once
1844 103 reserved twice (V-103, V-104)
1845 But total number of distinct locations are three.
1846 I should have atleast one customer who reserves in all these three locations.
1847 V-103, V-104 reserved by 103 cid belongs to same location Blore
1848 So we can’t consider 103 is reserved from 2 different locations
1849 So I can insert data here for
1850 101 for surat and vailakavali or
1851 102 for Blore and vailakavali or
1852 103 for Surat and vailakavali so on
1853SQL>insert into reservation values ('103','V-102','12-MAR-2016','14-MAR-2016');
1854SQL>insert into reservation values ('103','V-105','15-APR-2016','20-APR-2016');
1855SQL> select * from reservation;
1856OUTPUT:
1857CID VID START_DAT END_DATE
1858101 V-101 10-JAN-11 10-FEB-15
1859102 V-102 12-MAR-11 10-JUN-16
1860103 V-103 15-FEB-09 09-SEP-15
1861105 V-105 15-FEB-13 09-SEP-15
1862103 V-104 15-OCT-14 24-OCT-14
1863103 V-102 12-MAR-16 14-MAR-16
1864103 V-105 15-APR-16 20-APR-16
1865i. Retrieve the locations that have at least one vehicle with manual transmission that
1866has lower mileage than any luxury vehicle at that location.
1867SQL> select * from vehicle v1
1868Database Applications Lab REVA University
1869School of Computing and Information Technology Page 58
1870 where v1.vsize not in (‘luxury’) and v1.transmission=’manual’
1871 and v1.mileage<(select min(mileage) from vehicle v2 where v2.vsize=’luxury’ and
1872 v1.location=v2.location);
1873OUTPUT:
1874VID MILEAGE LOCATION VSIZE TRANSMISSION
1875V-103 10 Blore mid-size manual
1876Explanation: This Query displays all the locations that have at least one vehicle with manual
1877transmission that has lower mileage than any luxury vehicle at that location by using
1878subquery.
1879j. Delete all the reservations for customer whose last name starts with ‘S’ or only S.
1880delete from reservation where cid in (select cid from carcustomer where last_name like
1881'S%');
1882OUTPUT:
1883RESERVATION TABLE BEFORE 'DELETE' OPERATION :=====>
1884CID VID START_DAT END_DATE
1885101 V-101 10-JAN-11 10-FEB-15
1886102 V-102 12-MAR-11 10-JUN-16
1887103 V-103 15-FEB-09 09-SEP-15
1888105 V-105 15-FEB-13 09-SEP-15
1889103 V-104 15-OCT-14 24-OCT-14
1890103 V-102 12-MAR-16 14-MAR-16
1891103 V-105 15-APR-16 20-APR-16
1892RESERVATION TABLE AFTER 'DELETE' OPERATION :=====>
1893CID VID START_DAT END_DATE
1894101 V-101 10-JAN-11 10-FEB-15
1895102 V-102 12-MAR-11 10-JUN-16
1896103 V-103 15-FEB-09 09-SEP-15
1897103 V-104 15-OCT-14 24-OCT-14
1898103 V-102 12-MAR-16 14-MAR-16
1899103 V-105 15-APR-16 20-APR-16
1900Explanation: This Query Delete all the reservations for customer whose last name starts
1901with ‘S’ or only S and the output is shown for the same, before deletion and after deletion.
1902Database Applications Lab REVA University
1903School of Computing and Information Technology Page 59
19048. Lab Assignments
1905SL.No. Practice & Assignment Queries
19061 Assignment Queries for Lab Exercise I
1907a. Retrieve the complete data from Customer table.
1908b. Retrieve the complete data from Agent table by mentioning attributes.
1909c. Display only product id and product name.
1910d. Display Product name and price as a single column and the column name
1911be “Product and their prices” (concatenation operator and alias).
1912e. Display the city names of the customers by eliminating duplicates.
1913f. Retrieve the names of the customers lives in “Mumbai”.
1914g. Display agent ids and names belongs to New Delhi.
1915h. Retrieve customer ids who ordered both “P01” and “P02”.
1916i. Get customers whose name begins with letter "A".
1917j. Retrieve the customers whose name starts with letter “A” and third letter
1918is “a” eg. Amar.
1919k. Retrieve the customers whose name consists of letter “a” eg. Amar,
1920Anand.
1921l. Get customer ids whose discount is between 8 and 10.
1922m. Display the product name whose price is 10 or 20 using IN and OR
1923operators.
1924n. Get total quantity of product "p01" that has been ordered.
1925o. Get number of cities in which customers are based.
1926p. Get total amount of all orders.
1927q. Get total number of customers.
1928r. Get average discount value for customers.
1929s. Get agent ids with the smallest percent commission.
1930t. Display the customer names who placed an order through the agent who
1931is having aid as “a01”.
1932u. Retrieve the names of the customers who live in “Mumbai” and order
1933product “P01”.
1934v. Retrieve customer ids who do not order part “P01”.
19352 Assignment Queries for Lab Exercise II
1936a. List female employees from dept no is 2 earning more than Rs.35000
1937b. Retrieve the names and address of all employee who work for the
1938‘Research’ department.
1939c. Retrieve the names and salary of all employees who work in department
1940number 5.
1941d. Retrieve the names of the employees and their superSSN name
1942e. Display name as "Employee name" and salary for the year as "Annual
1943Income"
1944f. Display name, current salary and salary if it is going to be increased by
1945800 rupees
1946g. Display Department name and number as a single column with the name
1947as “Department Details”
1948Database Applications Lab REVA University
1949School of Computing and Information Technology Page 60
1950h. Retrieve the names of the managers who have more than two
1951dependents.
1952i. Retrieve the names of the managers with atleast one dependent.
1953j. List all the Projects on which employee Ram is working
1954k. Retrieve the names of the employees who work on any project that
1955Kumar works.
1956l. Retrieve the names of the employees who do not have supervisor
1957m. Count the number of distinct salary values in the database.
1958n. For each project, retrieve the project number, the project name, and the
1959number of employees from department 3 who work on the project.
1960o. Retrieve all employees in department 3 whose salary is between
1961Rs.35,000 and Rs.40,000.
19629. Solutions for Lab Assignment Exercise I:
1963a) Retrieve the complete data from customer table
1964SQL> select * from customer;
1965OUTPUT:
1966CID CNAME CITY DISCOUNT
1967c001 Sobhit Darjeling 10
1968c002 Bhanu Srinagar 12
1969c003 Amar Srinagar 8
1970c004 Anand Darjeling 8
1971c005 Anand Mumbai 0
1972b) Retrieve the data complete from Agents by mentioning attributes
1973SQL>select aid, aname, city, percent from agent;
1974OUTPUT:
1975AID ANAME CITY PERCENT
1976a01 Sonu NewDelhi 6
1977a02 John Agra 6
1978a03 Bhargav Jaipur 7
1979a04 Gaurav NewDelhi 6
1980a05 Omkar Srinagar 5
1981a06 Sonu Darjeling 5
19827 rows selected.
1983c) Display only product id and product name
1984SQL> select pid,pname from product;
1985OUTPUT:
1986PID PNAME
1987Database Applications Lab REVA University
1988School of Computing and Information Technology Page 61
1989p01 comb
1990p02 brush
1991p03 eraser
1992p04 pen
1993p05 pencil
1994p06 folder
1995p07 Highlighter
19967 rows selected.
1997d) Display Product name and price as a single column and the column name be
1998“Product and their prices” (concatenation operator and alias)
1999SQL> select pname||price "product and their prices" from product;
2000OUTPUT:
2001product and their prices
2002comb 10
2003brush 20
2004eraser 2
2005pen 15
2006pencil 3
2007folder 15
2008Highlighter 20
20097 rows selected.
2010If some space has to be included between 2 column values after concatenating in the
2011output, use number of spaces required within the single quote along with
2012concatenation operator.
2013SQL> select pname||' '||price "product and their prices" from product;
2014OUTPUT:
2015product and their prices
2016comb 10
2017brush 20
2018eraser 2
2019pen 15
2020pencil 3
2021folder 15
2022Highlighter 20
2023Database Applications Lab REVA University
2024School of Computing and Information Technology Page 62
20257 rows selected.
2026SQL> select pname||' cost is '||price "product and their prices" from product;
2027OUTPUT:
2028product and their prices
2029comb cost is 10
2030brush cost is 20
2031eraser cost is 2
2032pen cost is 15
2033pencil cost is 3
2034folder cost is 15
2035 Highlighter cost is 20
20368 rows selected.
2037e) Display the city names of the customers by eliminating duplicates
2038
2039SQL> select distinct city from customer;
2040OUTPUT:
2041CITY
2042Srinagar
2043Darjeling
2044Mumbai
2045Srinagar
2046 f)Retrieve the names of the customers lives in “Mumbai”
2047
2048SQL> select cname from customer where city='Mumbai';
2049OUTPUT:
2050CNAME
2051Anand
2052g) Display agent ids and names belongs to New Delhi
2053SQL> select aid, aname from agent where city = 'NewDelhi';
2054OUTPUT:
2055AID ANAME
2056a01 Sonu
2057a04 Gaurav
2058h) Retrieve the customer ids who ordered the products “p01” and “p02”
2059SQL> select cid from orders where pid='p01' intersect select cid from orders where
2060pid='p07';
2061Database Applications Lab REVA University
2062School of Computing and Information Technology Page 63
2063OUTPUT:
2064CID
2065c001
2066c005
2067i) Get customers whose name begins with letter "A".
2068SQL> select cname from customer where cname like 'A%';
2069OUTPUT:
2070CNAME
2071Amar
2072Anand
2073Anand
2074j)Retrieve the customers whose name starts with letter “A” and third letter is “a” eg.
2075Amar
2076SQL> select cname from customer where cname like 'A_a%';
2077OUTPUT:
2078CNAME
2079Amar
2080Anand
2081Anand
2082
2083k)Retrieve the customers whose name consists of letter “a”
2084SQL> select cname from customer where cname like '%a%';
2085OUTPUT:
2086CNAME
2087Bhanu
2088Amar
2089Anand
2090Anand
2091 l)Get customer ids whose discount is between 8 and 10.
2092SQL> select cid from customer where discount between 8 and 10;
2093OUTPUT:
2094CID
2095c001
2096c003
2097Database Applications Lab REVA University
2098School of Computing and Information Technology Page 64
2099c004
2100m) Display the product name whose price is 10 or 20 using IN and OR operators
2101SQL> select pname from product where price in (10,20);
2102 or
2103SQL> select pname from product where price=10 or price=20;
2104OUTPUT:
2105PNAME
2106comb
2107brush
2108Highlighter
2109 n) Get total quantity of product "p01" that has been ordered.
2110 SQL> select sum(qty) from orders where pid='p01'
2111OUTPUT:
2112 SUM(QTY)
2113 4800
2114o) Get number of cities in which customers are based.
2115SQL> select count ( distinct ( cname ) ) from customer;
2116OUTPUT:
2117COUNT(DISTINCT(CNAME))
2118 4
2119 p) Get total amount of all orders.
2120SQL> select sum(ordamount) from orders;
2121OUTPUT:
2122SUM(ORDAMOUNT)
2123 104598
2124 q)Get total number of customers.
2125SQL> select count(cid) from customer;
2126OUTPUT:
2127COUNT(CID)
2128 5
2129 r)Get average discount value for customers.
2130SQL> select avg(discount) from customer;
2131OUTPUT:
2132AVG(DISCOUNT)
2133 7.6
2134Database Applications Lab REVA University
2135School of Computing and Information Technology Page 65
2136s)Get agent ids with the smallest percent commission.
2137SQL> select aid from agent where percent in (select min(percent) from agent);
2138 Or select aid from agent where percent= (select min(percent) from agent)
2139OUTPUT:
2140AID
2141a05
2142a06
2143t)Display the names of the customers who placed an order through the agent who is
2144having aid as “a01”
2145SQL>Select distinct(c.cname) from customer c , orders o where c.cid=o.cid and
2146o.aid=’a01’;
2147OUTPUT:
2148CNAME
2149Sobhit
2150Anand
2151u)Retrieve the names of the customers who live in “Mumbai” and order product “p01”
2152SQL> select cname from customer c , orders o where c.city='Mumbai' and c.cid=o.cid
2153and o.pid='p01';
2154OUTPUT:
2155CNAME
2156Anand
2157Anand
2158SQL> select distinct(cname) from customer c,orders o where c.city='Mumbai' and
2159c.cid=o.cid and o.pid='p01';
2160OUTPUT:
2161CNAME
2162Anand
2163Anand
2164v) Retrieve customer ids who do not order part “p01”
2165select cid from customer minus (select cid from orders where pid='p01') ;
2166or
2167select cid from customer minus ( select distinct (cid) from orders where pid='p01');
2168OUTPUT:
2169CID
2170c002
2171c003
2172Database Applications Lab REVA University
2173School of Computing and Information Technology Page 66
217410.Viva Voce Questions
21751) What is an RDBMS?
21762) What is the SQL?
21773) What are the different kinds of DBMS?
21784) What are the features of relational database?
21795) What are data types?
21806) What is an E-R diagram?
21817) What is the referential integrity?
21828) What is a foreign key?
21839) What is a primary key?
218410) What is an alternate key in table?
218511) What is the normalization?
218612) Explain the First Normal Form?
218713) Explain the Second Normal Form?
218814) Explain the Third Normal Form?
218915) What is an index, and how is it used to improve performance?
219016) What are the types of indexes, and if separate indexes are created on each
2191column of a table, what are the advantages and disadvantages of this
2192approach?
219317) What is the SQL Data Manipulation Language (DML)?
219418) What is the SQL Data Definition Language (DDL)?
219519) What is the de-normalization?
219620) What is a transaction?
219721) What are ACID properties?
219822) What is the difference between DELETE TABLE and TRUNCATE TABLE
2199commands?
220023) What are constraints?
220124) What are the different types of constraints?
220225) What are cursors? What are the different types of cursors?
220326) What are the advantages of cursors? How can you avoid cursors?
220427) What is a join and explain different types of joins.
220528) What is a self-join? Explain it with an example.
2206Database Applications Lab REVA University
2207School of Computing and Information Technology Page 67
220829) How do you implement one-to-one, one-to-many, many-to-many
2209relationships while designing tables?
221030) What is the difference between primary key and a unique key?
221131) What are defaults?
221232) What are triggers? How do you invoke a trigger on demand?
221333) What is a stored procedure? What are the advantages?
221434) What is the difference between stored procedure and a trigger?
221535) What are the different types of parameters available in stored procedures?
221636) How do you get the distinct rows in a table/ resultset?
221737) How do you get the distinct rows without using the keyword DISTINCT?
221838) How can you get the duplicated rows from the table using a single query?
221939) How can you get the total number of records in a table?
222040) How can you insert values in multiple rows using one insert statement?
222141) What is the database replication?
222242) What will happen when a Rollback statement is executed inside a Trigger?
2223Recommended Learning Resources:
22241. Raghu Ramakrishnan and Johannes Gehrke,Database Management Systems, 3rd
2225Edition, McGraw-Hill, 2003.
22262. Elmasri and Navathe,Fundamentals of Database Systems, 5th Edition, Pearson
2227Education, 2007.
22283. Abraham Silberschatz, Henry F. Korth, S. Sudarshan: Database System Concepts, 6th
2229Edition, McGraw Hill, 2010.
2230References:
22311. Christopher J Date, An Introduction to Database Systems
22322. J. D. Ullman, “Principles of Database Systems”
22333. Serge Abiteboul, Richard Hull and Victor Vianu ,“Foundations of Databases”
22344. Bipin C Desai, An Introduction to Database Systems
22355. Mark L Gillenson, Fundamentals of Database Management Systems
22366. Thomas Connolly and Carolyn Begg, Data base Solutions: A step by step Guide to
2237Building Data bases
22387. C J Date, Database Design and Relational Theory: Normal Forms and All that Jazz, O
2239‘Reilly, April 2012.