ihqCtyYQ

1Jyothy Institute of Technology
2(Affiliated to V.T.U., Belgaum)
3Tataguni, Bangalore - 560 082
4A
5MANUAL FOR
65 th SEMESTER
710CSL57
8DATABASE APPLICATIONS LABORATORY
9Department of Computer Science and Engineering
102016-2017TABLE OF CONTENTS
11Sl. No
1201
1302
1403
1504
1605
1706
1807
1908
2009
2110
2211
23Description
24Syllabus
25Introduction and Installation of MySql
26MySqlCommands
27MySql Datatypes
28STUDENT DATA BASE
29AIRLINE FLIGHT DATABASE
30STUDENT ENROLLMENT DATA BASE
31BOOK DEALER DATABASE
32BANKING ENTERPRISE DATABASE
33Viva Voice Questions
34Applications of DBMS
35Page Nos.
361
372
384
3910
4012
4118
4224
4330
4436
4542
4650Database Applications Laboratory
472016-17
48SYLLABUS
49DATABASE APPLICATION LABORATORY
50(Common to CSE & ISE)
51Subject Code: 10CSL57
52Hours/Week : 03
53Total Hours : 42
54I. Consider the following relations:
55I.A. Marks: 25
56Exam Hours: 03
57Exam Marks: 50
58I. Consider the following relations:
59STUDENT (snum: integer, sname: string, major: string, level: string, age:
60integer) CLASS (name: string, meets at: string, room: string, d: integer)
61ENROLLED (snum: integer, cname: string)
62FACULTY (fid: integer, fname: string, deptid:
63integer)
64The meaning of these relations is straightforward; for example, Enrolled has one record per student-class
65pair such that the student is enrolled in the class. Level is a two character code with 4 different values
66(example: Junior: JR etc)
67Write the following queries in SQL. No duplicates should be printed in any of the answers.
68(i) Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof.Harshith
69(ii) Find the names of all classes that either meet in room R128 or have five or more Students enrolled.
70(iii) Find the names of all students who are enrolled in two classes that meet at the same time.
71(iv) Find the names of faculty members who teach in every room in which some class is taught.
72(v) Find the names of faculty members for whom the combined enrollment of the courses that they teach
73is less than five.
74ENT (string, major: string, level: string, age: integer)
75II. The following relations keep track of airline flight information:
76FLIGHTS (no: integer, from: string, to: string, distance: integer, Departs:
77time, arrives: time, price: real)
78AIRCRAFT (aid: integer, aname: string, cruisingrange:
79integer) CERTIFIED (eid: integer, aid: integer)
80EMPLOYEES (eid: integer, ename: string, salary: integer)
81Note that the Employees relation describes pilots and other kinds of employees as
82well; Every pilot is certified for some aircraft, and only pilots are certified to fly.
83Write each of the following queries in SQL.
84(i) Find the names of aircraft such that all pilots certified to operate them have salaries more than
85Rs.80, 000.
86(ii) For each pilot who is certified for more than three aircrafts, find the eid and the
87maximum cruisingrange of the aircraft for which she or he is certified.
88(iii) Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru
89to Frankfurt.
90(iv) For all aircraft with cruisingrange over 1000 Kms, .find the name of the aircraft and the
91average salary of all pilots certified for this aircraft.
92(v) Find the names of pilots certified for some Boeing aircraft.
93Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi
94III. Consider the following database of student enrollment in courses and books adopted
95foreach course.
96STUDENT (regno: string, name: string, major: string, bdate:
971Database Applications Laboratory
982016-17
99date) COURSE (course#: int, cname: string, dept: string)
100ENROLL (regno: string, course#: int, sem: int, marks: int)
101BOOK_ADOPTION (course#: int, sem: int, book-ISBN:
102int)
103TEXT (book-ISBN: int, book-title: string, publisher: string, author: string)
104(i) Create the above tables by properly specifying the primary keys and the foreign keys.
105(ii) Enter atleast five tuples for each relation.
106(iii) Demonstrate how you add a new text book to the database and make this book be adopted by
107some department.
108(iv) Produce a list of text books (include Course#, Book-ISBN, Book-title) in the alphabetical order
109for courses offered by the  ̳CS‘ department that use more than two books.
110(v) List any department that has all its adopted books published by a specific publisher.
111(vi) Generation of suitable reports.
112Create suitable front end for querying and displaying the
113results. LASS (name: string, meets at: string, room: string, d:
114integer)
115IV. The following tables are maintained by a book
116dealer.AUTHOR (author-id: int, name: string, city: string,
117country: string)
118PUBLISHER (publisher-id: int, name: string, city: string, country: string)
119CATALOG (book-id: int, title: string, author-id: int, publisher-id: int, category-id: int, year: int, price:
120int) CATEGORY (category-id: int, description: string)
121ORDER-DETAILS (order-no: int, book-id: int, quantity: int)
122i. Create the above tables by properly specifying the primary keys and foreign keys.
123ii. Enter atleast five tuples for each tables.
124iii. Give the details of the authors who have 2 or more books in the catalog and the price of the books
125is greater than the average price of the books in the catalog and the year of publication is after 2000.
126iv. Find the author of the book which has maximum sales.
127v. Demonstrate how you increase the price of books published by a specific publisher by 10%.
128vi. Generation of suitable reports
129Create suitable front end for querying and displaying the results.
130V. Consider the following database for a banking enterprise.
131BRANCH (branch-name: string, branch-city: string, assets:
132real) ACCOUNT (accno: int, branch-name: string, balance:
133real) DEPOSITOR (customer-name: string, accno: int)
134CUSTOMER (customer-name: string, customer-street: string, customer-city:
135string) LOAN (loan-number: int, branch-name: string, amount: real)
136BORROWER (customer-name: string, loan-number: int)
137i. Create the above tables by properly specifying the primary keys and foreign keys.
138ii. Enter atleast five tuples for each relation.
139iii. Find all the customers who have atleast two accounts at the main branch.
140iv. Find all the customers who have an account at all the branches located in a specific city.
141v. Demonstrate how you delete all account tuples at every branch located in a specific city.
142vi. Generation of suitable reports.
143Create suitable front end for querying and displaying the results.
144Instructions:
1451. The exercises are to be solved in an RDBMS environment like Oracle or DB2.
1462Database Applications Laboratory
1472016-17
1482. Suitable tuples have to be entered so that queries are executed correctly.
1493. Front end may be created using either VB or VAJ or any other similar tool.
1504. The student need not create the front end in the examination. The results of the queries may
151be
152displayed directly.
1535. Relevant queries other than the ones listed along with the exercises may also be asked
1546. Questions must be asked based on lots.
155MySQL Database:
156MySQL is a freely available open source Relational Database Management System (RDBMS) that
157usesStructured Query Language (SQL). SQL is the most popular language for adding, accessing and
158managing content in a database. It is most noted for its quick processing, proven reliability, ease and
159flexibility of use. MySQL is a fast, easy-to-use RDBMS being used for many small and big businesses.
160MySQL is developed, marketed, and supported by MySQL AB, which is a Swedish company. MySQL is
161becoming so popular because of many good reasons:
162MySQL is released under an open-source license. So you have nothing to pay to use it.
163MySQL is a very powerful program in its own right. It handles a large subset of the functionality
164of the most expensive and powerful database packages.
165MySQL uses a standard form of the well-known SQL data language.
166MySQL works on many operating systems and with many languages including PHP, PERL, C,
167C++, JAVA, etc.
168MySQL works very quickly and works well even with large data sets.
169MySQL is very friendly to PHP, the most appreciated language for web development.
170MySQL supports large databases, up to 50 million rows or more in a table. The default file size
171limit for a table is 4GB, but you can increase this (if your operating system can handle it) to a
172theoretical limit of 8 million terabytes (TB).
173MySQL occupies very less disk space.
174MySQL is customizable. The open-source GPL license allows programmers to modify the
175MySQL software to fit their own specific environments.
176How to Install MySQL on Ubuntu
177Using terminal:
178To install MySQL, run the following command from a terminal prompt:
179sudo apt-get install mysql-server
1803Database Applications Laboratory
1812016-17
182During the installation process you will be prompted to enter a password(for ex: root) for the MySQL
183root user. Once the installation is complete, the MySQL server should be started automatically.
184How to Access the MySQL shell
185Once you have MySQL installed on your droplet, you can access the MySQL shell by typing the
186following command into terminal:
187mysql -u root -p
188Enter the mysql password: root
189-u root -p refers to user root password. Login to MySQL as root user(u) with mysql password(p).
190Points to Remember
191All MySQL commands end with a semicolon; if the phrase does not end with a semicolon, the
192command will not execute.
193Also, although it is not required, MySQL commands are usually written in uppercase and
194databases, tables, user names, or text are in lowercase to make them easier to distinguish.
195However, the MySQL command line is not case sensitive.
196Commands in the command line are not case sensitive. But the tables and database names are
197case sensitive. The table name ―studǁ is not the same as ―STUDǁ.
198Summary of MySQL Commands
199Commands at the Database-Level
2001: To
201Delete
202(irrecoverable!)
203the
204DROP
205database
206DATABASE
207databaseName
2082: Delete if it exists
209DROP DATABASE IF EXISTS databaseName
2103: Create a new database
211CREATE DATABASE databaseName
2124: Create only if it does not exists
213CREATE DATABASE IF NOT EXISTS databaseName
2145: Show all the databases in this
2154Database Applications Laboratory
2162016-17
217server SHOW DATABASES
2186: Set
219the
220default
221(current)
222database USE databaseName
2237: Show the default database
224SELECT DATABASE()
2258: Show the CREATE DATABASE statement
226SHOW
227CREATEDATABASE
228DatabaseName
229Commands at the Table-Level
2301: To Delete the table (irrecoverable!)
231DROP TABLE [IF EXISTS] tableName,
232...
2332: 3: Create a new table
234CREATE TABLE [IF NOT EXISTS] tableName(
235columnName columnType columnAttribute, ...
236PRIMARY KEY(columnName),
237FOREIGN KEY (columnNmae) REFERENCES tableName (columnNmae)
238)
2393: Show all the tables in the default database
240SHOW TABLES
2414: Describe the details for a
242table
243DESCRIBE|DESC
244tableName
2455: Modify a table, e.g., ADD COLUMN and DROP COLUMN
2461: ALTER TABLE tableName ...
2472: ALTER TABLE tableName ADD columnDefinition
2483: ALTER TABLE tableName DROP columnName
2494: ALTER TABLE tableName ADD FOREIGN KEY (columnNmae) REFERENCES tableName
250(columnNmae)
2515: ALTER TABLE tableName DROP FOREIGN KEY constraintName
2526: Show the CREATE TABLE statement for this tableName
2535Database Applications Laboratory
2542016-17
255SHOW CREATE TABLE tableName
256Commands at the Row-Level
2571: Insert on all Columns
258INSERT INTO tableName VALUES (column1Value,
259column2Value,...) 2: Insert multiple rows
260INSERT INTO tableName VALUES (column1Value, column2Value,...),
261column2Value,...)...
262(column1Value,
2633: Insert on selected Columns
264INSERT INTO tableName (column1Name, ...,columnNName)VALUES (column1Value, ...,
265columnNValue)
2664: Deleting particular row
267DELETE FROM tableName WHERE criteria
2685: UPDATE tableName SET columnName = expr, ... WHERE criteria
2696: SELECT * | column1Name AS alias1, ..., columnNName AS
270aliasN FROM tableName
271WHERE criteria
272GROUP BY
273columnName
274ORDER BY columnName ASC|DESC, ...
275HAVING
276groupConstraints LIMIT
277count | offset count
278Others
2791: Show the warnings of the previous statement
280SHOW WARNINGS;
281Help & Exit Command
2821: To get any information about the commands used in the mysql use "help"
283command. Eg: help insert;
284help create;
2852: Type exit to exit from the MySQL prompt.
286How to Create and Delete a MySQL Database
287To check what databases are available
288show databases; mysql>
2896Database Applications Laboratory
2902016-17
291show databases;
292+--------------------+
293| database
294|
295+--------------------+
296| information_schema |
297| mysql |
298| performance_schema |
299| test
300|
301+--------------------+
3024 rows in set (0.01 sec)
303creating a database
304create database database name;
305(in this case, for example, we will call our database "company.")
306create database company;
307mysql> show databases;
308+-------------------- +
309| database
310|
311+-------------------- +
312| information_schema |
313| company
314|
315| mysql
316|
317| performance_schema |
318| test
319|
320+-------------------- +
3215 rows in set (0.00 sec)
322deleting a mysql database
323drop database database name;
324drop database database company;
325how to access a mysql database to open up the database we want to use:
326use company;
327To check the overview of the tables that the database contains.
328show tables;
329How to create a mysql table
330To create a employee table inside company database.
331create table employee
332(
333id int not null primary key
334auto_increment, name varchar(20),
3357Database Applications Laboratory
3362016-17
337city
338varchar(30),
339gender char(1)
340dob not null
341date );
342This command accomplishes a number of things:
3431: It has created a table called employee within the directory, company.
3442: We have set up 5 columns in the table—id, name, city, gender, dob.
3453: The ―idǁ column has a command (INT NOT NULL PRIMARY KEY AUTO_INCREMENT) that
346automatically numbers each row.
3474: The ―nameǁ column has been limited by the VARCHAR command to be under 20 characters long.
3485: The ―cityǁ column records the city that each employee belongs to. The VARCHAR limits text to be
349under 30 characters.
3506: The ―genderǁ column records the gender of the employee with single character, M or F.
3517: The ―dobǁ column will show the date of birth of the employee. MySQL requires that dates be written
352as yyyy-mm-dd
353mysql> SHOW TABLES;
354+------------------
355+
356| Tables_in_events |
357+------------------
358+
359| employee
360|
361+
362+------------------
3631 row in set (0.01 sec)
364How to display the table’s organization
365mysql>DESCRIBE employee;
366-
367-
368-
369-
370- +----------------
371+------------- +--------------- +------ +
372+
373| Field| Type
374| Null | Key | Default | Extra
375|
376+------------- +------------- +------+-----
377+---------------- +
378| id
379| int(11)
380| NO | PRI | NULL
381| auto_increment |
382|
383+ -
384----
385----
386+
387-
388----
389----
390| name
391| varchar(20) | YES
392|
393N
394U
395L
396L
397| |
398| |
399|
400| city
401| varchar(30) | YES |
402N
403U
404L
405L
4068Database Applications Laboratory
4072016-17
408|
409| gender
410| char(1)
411| YES
412|
413N
414U
415L
416L
417|
418|
419| dob
420| date
421| NO |
4220 |
423+------------- +------------- +------+----- --------- + +---------------- +
4245 rows in set (0.01 sec)
425|
426|
427How to Add Information to a MySQL Table
428To add information to a table, the command used is ―insertǁ.
4291: Insert a row with all the column values.
430Syntax: INSERT INTO tableName VALUES (firstColumnValue, ...,lastColumnValue)
431mysql>INSERT INTO employee VALUES (1001, ―kumarǁ, ―Bangaloreǁ, ǁMǁ ,  ̳2012-04-11‘);
4322: Inserting multiple rows in one command. Inserting NULL to the auto_increment column results in
433max_value + 1
434Syntax:
435INSERT INTO tableName VALUES
436(row1FirstColumnValue,
437...,row1lastColumnValue),
438(row2FirstColumnValue, ...,
439row2lastColumnValue),
440...
441mysql>INSERT INTO employee VALUES
442(NULL, 'Santhosh', 'Tumkur', "M", 1992-05-
44312), (NULL, 'vandana', 'Hasan', "F", 1992-05-
44412);
4453: To insert a row with values on selected columns only. Missing value for the auto_increment column
446also results in max_value + 1
447Syntax:
448INSERT INTO tableName (column1Name, ...,columnNName) VALUES (column1Value, ...,
449columnNValue)
450mysql> INSERT INTO employee (name, city) VALUES ('Santhosh', 'Tumkur'),( 'vandana', 'Hasan');
451Alternately, use SET to set the values
452INSERT INTO tableName SET column1=value1, column2=value2, ...
4534: Missing columns get their default values
454mysql> INSERT INTO products (name, city) VALUES ('Nuthan', 'Tumkur');
4555: Remove the row
456mysql> DELETE FROM employee WHERE id = 1003;
457mysql> select * from employee;
458+------ +---------- +-----------+-------- +------------
459+
460| id | name
461| city| gender | dob
462|
463+------ +---------- +-----------+-------- +------------
464+
465| 1001 | kumar | Bangalore | M
466| 0000-00-00 |
467| 1002 | santhosh | Tumkur | M
468| 0000-00-00 |
469| 1003 | nuthan | Hasan | M
470| 0000-00-00 |
471+------ +---------- +-----------+-------- +------------
472+
4739Database Applications Laboratory
4742016-17
4753 rows in set (0.00 sec)
476Querying the Database - SELECT
4771: list all the rows of the specified columns.
478syntax : select column1name, column2name, ... from tablename
479mysql> select name,city from employee;
4802: list all the rows of all columns, * is a wildcard denoting all
481columns syntax: select * from tablename
482mysql> select * from employee;
4833: list rows that meet the specified criteria in where clause
484syntax: select column1name, column2name,... from tablename where criteria
485mysql> select name, city from employee where id=105;
486MySQL Data Types
4871: Numeric Data Types:
488INT :If signed, the allowable range is from -2147483648 to 2147483647. If unsigned,
489theallowable range is from 0 to 4294967295. You can specify a width of up to 11 digits.
490TINYINT :If signed, the allowable range is from -128 to 127. If unsigned, the allowable range
491isfrom 0 to 255. You can specify a width of up to 4 digits.
492SMALLINT :If signed, the allowable range is from -32768 to 32767. If unsigned, the
493allowablerange is from 0 to 65535. You can specify a width of up to 5 digits.
494MEDIUMINT :If signed, the allowable range is from -8388608 to 8388607. If unsigned,
495theallowable range is from 0 to 16777215. You can specify a width of up to 9 digits.
496BIGINT : If signed, the allowable range is from
497-9223372036854775808 to
4989223372036854775807. If unsigned, the allowable range is from 0 to 18446744073709551615.
499You can specify a width of up to 20 digits.
500FLOAT(M,D) : A floating-point number that cannot be unsigned. You can define the
501displaylength (M) and the number of decimals (D). This is not required and will default to 10,2,
502where 2 is the number of decimals and 10 is the total number of digits (including decimals).
503Decimal precision can go to 24 places for a FLOAT.
504DOUBLE(M,D) : A double precision floating-point number that cannot be unsigned. You
505candefine the display length (M) and the number of decimals (D). This is not required and will
506default to 16,4, where 4 is the number of decimals. Decimal precision can go to 53 places for a
507DOUBLE. REAL is a synonym for DOUBLE.
508DECIMAL(M,D) - An unpacked floating-point number that cannot be unsigned. In
509unpackeddecimals, each decimal corresponds to one byte. Defining the display length (M) and
510the number of decimals (D) is required. NUMERIC is a synonym for DECIMAL.
51110Database Applications Laboratory
5122016-17
5132: Date and Time Types:
514DATE - A date in YYYY-MM-DD format, between 1000-01-01 and 9999-12-31. For
515example,December 30th, 1973 would be stored as 1973-12-30.
516DATETIME - A date and time combination in YYYY-MM-DD HH:MM:SS format,
517between1000-01-01 00:00:00 and 9999-12-31 23:59:59. For example, 3:30 in the afternoon on
518December 30th, 1973 would be stored as 1973-12-30 15:30:00.
519TIMESTAMP - A timestamp between midnight, January 1, 1970 and sometime in 2037.
520Thislooks like the previous DATETIME format, only without the hyphens between numbers;
5213:30 in the afternoon on December 30th, 1973 would be stored as 19731230153000 (
522YYYYMMDDHHMMSS ).
523TIME - Stores the time in HH:MM:SS format.
524YEAR(M) - Stores a year in 2-digit or 4-digit format. If the length is specified as 2 (for
525exampleYEAR(2)), YEAR can be 1970 to 2069 (70 to 69). If the length is specified as 4, YEAR
526can be 1901 to 2155. The default length is 4.
5273: String Types:
528CHAR(M) - A fixed-length string between 1 and 255 characters in length (for
529exampleCHAR(5)), right-padded with spaces to the specified length when stored. Defining a
530length is not required, but the default is 1.
531VARCHAR(M) - A variable-length string between 1 and 255 characters in length; for
532exampleVARCHAR(25). You must define a length when creating a VARCHAR field.
533BLOB or TEXT - A field with a maximum length of 65535 characters. BLOBs are
534"BinaryLarge Objects" and are used to store large amounts of binary data, such as images or
535other types of files. Fields defined as TEXT also hold large amounts of data; the difference
536between the two is that sorts and comparisons on stored data are case sensitive on BLOBs and
537are not case sensitive in
538TEXT fields. You do not specify a length with BLOB or TEXT.
539TINYBLOB or TINYTEXT - A BLOB or TEXT column with a maximum length of
540255characters. You do not specify a length with TINYBLOB or TINYTEXT.
541MEDIUMBLOB or MEDIUMTEXT - A BLOB or TEXT column with a maximum length
542of16777215 characters. You do not specify a length with MEDIUMBLOB or MEDIUMTEXT.
543LONGBLOB or LONGTEXT - A BLOB or TEXT column with a maximum length of
5444294967295 characters. You do not specify a length with LONGBLOB or LONGTEXT.
545ENUM - An enumeration, which is a fancy term for list. When defining an ENUM, you
546arecreating a list of items from which the value must be selected (or it can be NULL). For
547example, if you wanted your field to contain "A" or "B" or "C", you would define your ENUM
548as ENUM ('A', 'B', 'C') and only those values (or NULL) could ever populate that field.
54911Database Applications Laboratory
5502016-17
551DATABASE 1 : College_database
552DESCRIPTION:
553The following relations keep track of students, their enrollment for classes along with faculty
554information.
555Student (snum: integer, sname: string, major: string, level: string, age:
556integer) Class (name: string, meets at: string, room: string, d: integer)
557Enrolled (snum: integer, cname: string)
558Faculty (fid: integer, fname: string, deptid: integer)
559NOTE: The meaning of these relations is straight forward.For example, Enrolled has one record per
560student-class pair such that the student is enrolled in the class. Level is a two character code with 4
561different values (example: Junior: JR etc)
562Queries:
563Write the following queries in SQL. No duplicates should be printed in any of the answers.
5641. Find the names of all juniors (level=Jr) who are enrolled for class taught by professor Harshith.
5652. Find the names of all classes that either meet in room128 or have 5 or more students enrolled.
5663. Find the names of all students who are enrolled in two classes that meet at same time.
5674. Find the names of faculty members who teach in every room in which some class is taught.
5685 .Find the names of the faculty members for whom the combined enrollment of the classes that they
569teach is less than five
570mysqlL> create table student (
571snum integer primary
572key, sname varchar(15),
573major varchar(10),
574level1 varchar(2),
575age integer
576);
577mysql> create table faculty (
578fid integer primary key,
579fname varchar(15),
580deptid integer
581);
582mysql> create table class (
583cname varchar(10) primary
584key, meetsat varchar(4),
585room varchar(5),
586fid integer,
58712Database Applications Laboratory
5882016-17
589foreign key(fid) references faculty(fid)
590);
591mysql> create table enrolled (
592snum integer,
593cname
594varchar(10),
595foreign
596key(snum)
597references
598student(snum),
599foreign
600key(cname)
601references
602class(cname),
603primary
604key(snum,cname)
605);
606mysql > desc student;
607+-------- +-------------
608+
609-----+--------- +------- +
610| Field | Type
611| Null
612| Key | Default | Extra |
613+-------- +-------------
614+----- +--------- +------- +
615| snum | int(11)
616| NO
617| PRI | NULL |
618|
619| sname | varchar(15) | YES
620|
621| NULL
622|
623|
624| major | varchar(10) | YES
625|
626| NULL |
627|
628| level1 | varchar(2) | YES
629|
630| NULL |
631|
632| age
633| int(11)
634| YES
635|
636| NULL |
637|
638+-------- +-------------
639+----- +--------- +------- +
6405 rows in set (0.00 sec)
641+----- -
642+----- -
643+----- -
644mysql > insert into student values (1,ǁarthiǁ, ―osǁ,ǁjrǁ, 21);
645mysql >insert into student values (2,ǁbhaveshǁ,ǁisǁ,ǁsrǁ, 22);
646mysql >insert into student values (3,ǁchanasyaǁ,ǁecǁ,ǁjrǁ, 21);
647mysql >insert into student values (4,ǁsowmyaǁ,ǁcsǁ,ǁsrǁ, 23);
648mysql >insert into student values (5,ǁsanjayǁ,ǁecǁ,ǁjrǁ, 21);
649mysql >insert into student values (10,ǁgiriǁ,ǁccnǁ,ǁjrǁ, 20);
650mysql >insert into student values (20,ǁashaǁ,ǁosǁ,ǁjrǁ, 30);
651mysql> select * from stud;
652+------+----------+-------+--------+------+
653| snum | sname | major | level1 | age |
654+------+----------+-------+--------+------+
655| 1 | arun | os | jr | 21 |
656| 2 | bhanu | ls | sr | 22 |
657| 3 | chanasya | ec | jr | 21 |
658| 4 | sowmya | cs | sr | 23 |
659| 5 | gowri | ccn | jr | 20 |
660| 6 | asha | os | jr | 30 |
661+------+----------+-------+--------+------+
6626 rows in set (0.00 sec)
663mysql> desc faculty;
664+--------+-------------+------+-----+---------+-------+
66513Database Applications Laboratory
6662016-17
667| Field | Type | Null | Key | Default | Extra |
668+--------+-------------+------+-----+---------+-------+
669| fid | int(11) | NO | PRI | NULL | |
670| fname | varchar(15) | YES | | NULL | |
671| deptid | int(11) | YES | | NULL | |
672+--------+-------------+------+-----+---------+-------+
6733 rows in set (0.00 sec)
674mysql> insert into faculty values(11,ǁbharathiǁ,111);
675mysql> insert into faculty values(22,ǁnirmalaǁ,222);
676mysql> insert into faculty values(33,ǁprof.Harshithǁ,333);
677mysql> insert into faculty values(44,ǁbhavanaǁ,111);
678mysql> insert into faculty values(55,ǁushaǁ,111);
679mysql> select * from faculty;
680+-----+---------------+--------+
681| fid | fname | deptid |
682+-----+---------------+--------+
683| 11 | bharathi | 111 |
684| 22 | nirmala | 222 |
685| 33 | prof.Harshith | 333 |
686| 44 | bhavana | 111 |
687| 55 | usha | 111 |
688+-----+---------------+--------+
6895 rows in set (0.00 sec)
690mysql> desc class;
691+---------+-------------+------+-----+---------+-------+
692| Field | Type | Null | Key | Default | Extra |
693+---------+-------------+------+-----+---------+-------+
694| cname | varchar(10) | NO | PRI | NULL | |
695| meetsat | varchar(4) | YES | | NULL | |
696| room | varchar(5) | YES | | NULL | |
697| fid | int(11) | YES | MUL | NULL | |
698+---------+-------------+------+-----+---------+-------+
6994 rows in set (0.00 sec)
700mysql> insert into class values(―ccnaǁ,ǁ10:00ǁ,ǁr126ǁ,33);
701mysql> insert into class values(―cobolǁ,ǁ10:00ǁ,ǁr127ǁ,22);
702mysql> insert into class values(―dotnetǁ,ǁ9:00ǁ,ǁr101ǁ,33);
703mysql> insert into class values(―javaǁ,ǁ11:15ǁ,ǁr126ǁ,22);
704mysql> insert into class values(―jcpǁ,ǁ12:15ǁ,ǁr128ǁ,22);
705mysql> insert into class values(―oopsǁ,ǁ12:15ǁ,ǁr127ǁ,22);
706mysql> insert into class values(―oracleǁ,ǁ12:15ǁ,ǁr128ǁ,11);
707mysql> insert into class values(―testiingǁ,ǁ11:15ǁ,ǁr101ǁ,22);
708mysql> select * from class;
709+----------+---------+------+------+
710| cname | meetsat | room | fid |
711+----------+---------+------+------+
712| ccna | 10:0 | r126 | 33 |
71314Database Applications Laboratory
7142016-17
715| cobol | 10:0 | r127 | 22 |
716| dotnet | 9:00 | r101 | 33 |
717| java | 11:1 | r126 | 22 |
718| jcp | 12:1 | r128 | 22 |
719| oops | 12:1 | r127 | 22 |
720| oracle | 12:1 | r128 | 11 |
721| testiing | 11:1 | r101 | 22 |
722+----------+---------+------+------+
7238 rows in set (0.00 sec)
724mysql> desc enrolled;
725+-------+-------------+------+-----+---------+-------+
726| Field | Type | Null | Key | Default | Extra |
727+-------+-------------+------+-----+---------+-------+
728| snum | int(11) | NO | PRI | 0 | |
729| cname|varchar(10) | NO | PRI | | |
730+-------+-------------+------+-----+---------+-------+
7312 rows in set (0.00 sec)
732mysql> insert into enrolled values(1,'cobol');
733mysql> insert into enrolled values(2,'cobol');
734mysql> insert into enrolled values(3,'cobol');
735mysql> insert into enrolled values(4,'cobol');
736mysql> insert into enrolled values(1,'dotnet');
737mysql> insert into enrolled values(1,'jcp');
738mysql> insert into enrolled values(5,'jcp');
739mysql> insert into enrolled values(3,'oracle');
740mysql> insert into enrolled values(5,'cobol');
741mysql> insert into enrolled values(4,'ccna');
742mysql> insert into enrolled values(3,'testing');
743mysql> insert into enrolled values(3,'java');
744mysql> select * from enrolled;
745+------+----------+
746| snum | cname |
747+------+----------+
748| 4 | ccna |
749| 1 | cobol |
750| 2 | cobol |
751| 3 | cobol |
752| 4 | cobol |
753| 5 | cobol
754| 1 | dotnet |
755| 3 | java |
756| 1 | jcp |
757| 5 | jcp |
758| 3 | oracle |
759| 5 | oracle |
760| 3 | testing |
761+------+----------+
76213 rows in set (0.00 sec)
76315Database Applications Laboratory
7642016-17
765Queries:
7661. Find the names of all Juniors (level = JR) who are enrolled in a class taught by Prof. Harshith
767mysql> select distinct s.sname
768from student s, class c, enrolled e, faculty f
769where s.snum = e.snum and e.cname = c.cname and c.fid = f.fid and
770f.fname = 'prof.harshith' and s.level1 = 'jr';
771+-------+
772| sname |
773+-------+
774| arun |
775+-------+
7761 row in set (0.00 sec)
7772. Find the names of all classes that either meet in room R128 or have five or more Students
778enrolled.
779mysql> select c.cname
780from class c where c.room = 'r128' or c.cname in
781(select e.cname from enrolled e group by e.cname having count(*) >= 5);
782+--------+
783| cname |
784+--------+
785| cobol |
786| jcp |
787| oracle |
788+--------+
7893 rows in set (0.00 sec)
7903. Find the names of all students who are enrolled in two classes that meet at the same time.
791mysql> select distinct s.sname
792from student s
793where s.snum in (select e1.snum
794from enrolled e1, enrolled e2, class c1, class c2
795where e1.snum = e2.snum and e1.cname<>e2.cname and e1.cname=c1.cname and e2.cname=c2.cname
796and c1.meetsat = c2.meetsat);
797+--------+
798| sowmya |
799| gowri |
800+--------+
8012 rows in set (0.00 sec)
8024. Find the names of faculty members who teach in every room in which some class is taught.
803mysql> select fname, count (distinct room)
804from class c, enrolled e, faculty f
805where c.fid=f.fid and c.cname=e.cname
806group by fname having count(distinct room) in (select count(distinct room)
807from class c, enrolled e
808where e.cname=c.cname);
80916Database Applications Laboratory
8102016-17
811+---------+----------------------+
812| fname | count(distinct room) |
813+---------+----------------------+
814| nirmala | 4 |
815+---------+----------------------+
8161 row in set (0.00 sec)
8175. Find the names of faculty members for whom the combined enrollment of the courses that they
818teach is less than five.
819mysql> select distinct f.fname
820from class c, enrolled e, faculty f
821where c.fid=f.fid and c.cname=e.cname
822group by f.fname having count(*)<5;
823+---------------+
824| fname |
825+---------------+
826| bharathi |
827| prof.Harshith |
828+---------------+
8292 rows in set (0.00 sec)
83017Database Applications Laboratory
8312016-17
832DATABASE 2 : Airline_Flight_database
833DESCRIPTION:
834The following relations keep track of airline flight information:
835Flights (no: integer, from: string, to: string, distance: integer, Departs: time, arrives: time, price: real)
836Aircraft (aid: integer, aname: string, cruisingrange: integer)
837Certified (eid: integer, aid: integer)
838Employees (eid: integer, ename: string, salary: integer)
839Note that the Employees relation describes pilots and other kinds of employees as well; Every pilot is
840certified for some aircraft, and only pilots are certified to fly.
841Write each of the following queries in SQL.
842i. Find the names of aircraft such that all pilots certified to operate them have salaries more than Rs.80,
843000.
844ii. For each pilot who is certified for more than three aircrafts, find the eid and the maximum
845cruisingrange of the aircraft for which she or he is certified.
846iii. Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru to
847Frankfurt.
848iv. For all aircraft with cruisingrange over 1000 Kms, .find the name of the aircraft and the average salary
849of all pilots certified for this aircraft.
850v. Find the names of pilots certified for some Boeing aircraft.
851vi. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.
852mysql> create table flights (
853no int primary key,
854fro varchar(15),
855too varchar(15),
856distance integer,
857departs char(6),
858arrives char(6),
859price real
860);
861mysql> create table aircraft (
862aid int primary key,
863aname varchar(15),
864crurange integer
865);
866mysql> create table employees (
867eid int primary key,
868cname varchar(10),
869salary integer
870);
871mysql> create table certified (
872eid int,
873aid int,
874foreign key(eid) references employees(eid),
875foreign key(aid) references aircraft(aid),
87618Database Applications Laboratory
8772016-17
878primary key(eid,aid)
879);
880mysql>describe flights;
881+----------+-------------+------+-----+---------+-------+
882| Field | Type | Null | Key | Default | Extra |
883+----------+-------------+------+-----+---------+-------+
884| no | int(11) | NO | PRI | NULL | |
885| fro | varchar(15) | YES | | NULL | |
886| too | varchar(15) | YES | | NULL | |
887| distance | int(11) | YES | | NULL | |
888| departs | char(6) | YES | | NULL | |
889| arrives | char(6) | YES | | NULL | |
890| price | double | YES | | NULL | |
891+----------+-------------+------+-----+---------+-------+
8927 rows in set (0.00 sec)
893mysql>insert into flights values(4,'bangalore','australia',3000,‘09:30‘,‘12:00‘,150000);
894mysql> insert into flights values(2,'delhi','new jersey',4000,‘01:30‘,‘09:00‘,120000);
895mysql> insert into flights values(3,'hyderbad','bangalore',700,‘02:30‘,‘03:30‘,5000);
896mysql> insert into flights values(5,'angalore','kolkota',1200,‘04:30‘,‘06:30‘,10000);
897mysql> insert into flights values(6,'bangalore','newdelhi',2000,‘05:30‘,‘07:30‘,15000);
898mysql> insert into flights values(6,'bangalore','frankfrut',4000,‘06:30‘,‘11:30‘,20000);
899mysql>select * from Flights;
900+----+-----------+------------+----------+---------+---------+--------+
901| no | fro | too | distance | departs | arrives | price |
902+----+-----------+------------+----------+---------+---------+--------+
903| 2 | delhi | new jersey | 4000 | 01:30 | 09:00 | 120000 |
904| 3 | hyderbad | bangalore | 700 | 02:30 | 03:30 | 5000 |
905| 4 | bangalore | australia | 3000 | 09.30 | 12.00 | 150000 |
906| 5 | bangalore | kolkota | 1200 | 04:30 | 06:30 | 10000 |
907| 6 | bangalore | newdelhi | 2000 | 05:30 | 07:30 | 15000 |
908| 7 | bangalore | frankfrut | 4000 | 06:30 | 11:30 | 20000 |
909+----+-----------+------------+----------+---------+---------+--------+
9106 rows in set (0.00 sec)
911mysql>describe aircraft;
912+----------+-------------+------+-----+---------+-------+
913| Field | Type | Null | Key | Default | Extra |
914+----------+-------------+------+-----+---------+-------+
915| aid | int(11) | NO | PRI | NULL | |
916| aname | varchar(15) | YES | | NULL | |
917| crurange | int(11) | YES | | NULL | |
918+----------+-------------+------+-----+---------+-------+
9193 rows in set (0.00 sec)
920mysql> insert into aircraft values(11, 'airindia', 4000);
921mysql> insert into aircraft values(22, 'boeing120', 5000);
922mysql> insert into aircraft values(33, 'kingfisher', 3000);
92319Database Applications Laboratory
9242016-17
925mysql> insert into aircraft values(44, 'jetairways', 4000);
926mysql> insert into aircraft values(55, 'boeing240', 6000);
927mysql> insert into aircraft values(66, 'bharath', 600);
928mysql> insert into aircraft values(77, 'bharath160', 800);
929mysql>select * from Aircraft;
930+-----+------------+----------+
931| aid | aname | crurange |
932+-----+------------+----------+
933| 11 | airindia | 4000 |
934| 22 | boeing120 | 5000 |
935| 33 | kingfisher | 3000 |
936| 44 | jetairways | 4000 |
937| 55 | boeing240 | 6000 |
938| 66 | bharath | 600 |
939| 77 | bharath160 | 800 |
940+-----+------------+----------+
9417 rows in set (0.00 sec)
942mysql>describe employees;
943+--------+-------------+------+-----+---------+-------+
944| Field | Type | Null | Key | Default | Extra |
945+--------+-------------+------+-----+---------+-------+
946| eid | int(11) | NO | PRI | NULL | |
947| cname | varchar(10) | YES | | NULL | |
948| salary | int(11) | YES | | NULL | |
949+--------+-------------+------+-----+---------+-------+
9503 rows in set (0.01 sec)
951mysql> insert into employees values(123, ―kumarǁ, 80000);
952mysql> insert into employees values(201, ―muruliǁ, 200000);
953mysql> insert into employees values(301, ―alexanderǁ, 200000);
954mysql> insert into employees values(401, ―mathewǁ, 60000);
955mysql> insert into employees values(501, ―philominaǁ, 2000);
956mysql>select * from Employees;
957+-----+-----------+--------+
958| eid | cname | salary |
959+-----+-----------+--------+
960| 123 | kumar | 80000 |
961| 201 | muruli | 200000 |
962| 301 | alexander | 200000 |
963| 401 | mathew | 60000 |
964| 501 | philomina | 2000 |
965+-----+-----------+--------+
9665 rows in set (0.01 sec)
967[
96820Database Applications Laboratory
9692016-17
970mysql>describe certified;
971+-------+---------+------+-----+---------+-------+
972| Field | Type | Null | Key | Default | Extra |
973+-------+---------+------+-----+---------+-------+
974| eid | int(11) | NO | PRI | 0 | |
975| aid | int(11) | NO | PRI | 0 | |
976+-------+---------+------+-----+---------+-------+
9772 rows in set (0.01 sec)
978mysql> insert into certified values(123, 11);
979mysql> insert into certified values(201, 22);
980mysql> insert into certified values(201, 33);
981mysql> insert into certified values(301, 33);
982mysql> insert into certified values(123, 44);
983mysql> insert into certified values(401, 44);
984mysql> insert into certified values(501,44);
985mysql> insert into certified values(123,55);
986mysql> insert into certified values(123, 66);
987mysql>select * from certified;
988+-----+-----+
989| eid | aid |
990+-----+-----+
991| 123 | 11 |
992| 201 | 22 |
993| 201 | 33 |
994| 301 | 33 |
995| 123 | 44 |
996| 401 | 44 |
997| 501 | 44 |
998| 123 | 55 |
99923 | 66 |
1000+-----+-----+
10019 rows in set (0.00 sec)
10021: Find the names of aircraft such that all pilots certified to operate them have salaries more than
1003Rs.80, 000.
1004mysql>select distinct a.aname
1005from aircraft a,certified c,employees e
1006where a.aid=c.aid
1007and c.eid=e.eid
1008and not exists
1009(select * from employees e1 where e1.eid=e.eid and e1.salary<80000);
1010+------------+
1011| aname |
1012+------------+
1013| boeing120 |
1014| kingfisher |
1015+------------+
10163 rows in set (0.00 sec)
101721Database Applications Laboratory
10182016-17
10192. For each pilot who is certified for more than three aircrafts, find the eid and the maximum
1020cruisingrange of the aircraft for which she or he is certified.
1021mysql> select c.eid, max(a.crurange)
1022from certified c, aircraft a
1023where c.aid = a.aid
1024group by c.eid
1025having count(*) > 3;
1026+-----+-----------------+
1027| eid | max(a.crurange) |
1028+-----+-----------------+
1029| 123 | 6000 |
1030+-----+-----------------+
10311 row in set (0.00 sec)
10323. Find the names of pilots whose salary is less than the price of the cheapest route from Bengaluru
1033to Frankfurt.
1034mysql> select distinct e.cname
1035from employees e
1036where e.salary < ( select min(f.price) from flights f
1037where f.fro= 'bangalore' and f.too= 'frankfrut');
1038+-----------+
1039| cname |
1040+-----------+
1041| philomina |
1042+-----------+
10431 row in set (0.00 sec)
10444. For all aircraft with cruisingrange over 1000 Kms, .find the name of the aircraft and the average
1045salary of all pilots certified for this aircraft.
1046mysql>select a.aid, avg (e.salary)
1047from aircraft a, certified c, employees e
1048where a.aid = c.aid and c.eid = e.eid and a.crurange > 1000
1049group by a.aid, a.aname);
1050+-----+----------------+
1051| aid | avg (e.salary) |
1052+-----+----------------+
1053| 11 | 80000.0000 |
1054| 22 | 200000.0000 |
1055| 33 | 200000.0000 |
1056| 44 | 47333.3333 |
1057| 55 | 80000.0000 |
1058+-----+----------------+
10595 rows in set (0.00 sec)
10605. Find the names of pilots certified for some Boeing aircraft.
1061mysql> select distinct e.cname
1062from employees e, certified c, aircraft a
1063where e.eid = c.eid and c.aid=a.aid and a.aname like 'boeing%';
1064+--------+
1065| cname |
1066+--------+
106722Database Applications Laboratory
10682016-17
1069| kumar |
1070| muruli |
1071+--------+
10722 rows in set (0.00 sec)
10736. Find the aids of all aircraft that can be used on routes from Bengaluru to New Delhi.
1074mysql> select a.aid from aircraft a
1075where a.crurange > ( select min(f.distance)
1076from flights f where f.frm='bengaluru'
1077and f.too='newdelhi');
1078+-----+
1079| aid |
1080+-----+
1081| 11 |
1082| 22 |
1083| 33 |
1084| 44 |
1085| 55 |
1086+-----+
10875 rows in set (0.00 sec)
108823Database Applications Laboratory
10892016-17
1090DATABASE 3 : Student_Enrollment_Database
1091DESCRIPTION:
1092Consider the following database of student enrollment in courses and books adopted for each course.
1093STUDENT (regno: string, name: string, major: string, bdate: date)
1094COURSE (course#: int, cname: string, dept: string)
1095ENROLL (regno: string, course#: int, sem: int, marks: int)
1096BOOK_ADOPTION (course#: int, sem: int, book-ISBN: int)
1097TEXT (book-ISBN: int, book-title: string, publisher: string, author: string)
10981: Create the above tables by properly specifying the primary keys and the foreign keys.
10992: Enter at least five tuples for each relation.
11003: Demonstrate how you add a new text book to the database and make this book be adopted by some
1101department.
11024: Produce a list of text books (include Course#, Book-ISBN, Book-title) in the alphabetical order for
1103courses offered by the  ̳CS‘ department that use more than two books.
11045: List any department that has all its adopted books published by a specific publisher.
11056: Generation of suitable reports.
11067: Create suitable front end for querying and displaying the results.
1107mysql>create table student
1108(
1109regno varchar(20) primary key not null,
1110sname varchar(20),
1111major varchar(20),
1112bdate date
1113);
1114mysql>create table course
1115( cno int primary key not null,
1116cname varchar(20),
1117dept varchar(20)
1118);
1119mysql>create table enroll
1120(
1121regno varchar(20),
1122cno int,
1123sem int,
1124marks int,
1125primary key(regno,cno),
1126foreign key (regno) references student (regno),
1127foreign key (cno) references course (cno)
1128);
1129mysql>create table text
1130(
1131book_isbn int primary key not null,
1132book_title varchar(20),
1133pubisher varchar(20),
1134author varchar(20)
1135);
113624Database Applications Laboratory
11372016-17
1138mysql>create table book_adoption
1139(
1140cno int,
1141sem int,
1142book_isbn int,
1143primary key(cno,book_isbn),
1144foreign key (cno) references course (cno),
1145foreign key (book_isbn) references text (book_isbn)
1146);
1147mysql>describe student;
1148+-------+-------------+------+-----+---------+-------+
1149| Field | Type | Null | Key | Default | Extra |
1150+-------+-------------+------+-----+---------+-------+
1151| regno | varchar(20) | NO | PRI | NULL | |
1152| sname | varchar(20) | YES | | NULL | |
1153| major | varchar(20) | YES | | NULL | |
1154| bdate | date | YES | | NULL ||
11554 rows in set (0.00 sec)
1156mysql>insert into student values ('1cg04is012','asha','networks',‘1986-06-07‘);
1157mysql>insert into student values ('1cg04is030','anitha','fafl',1984-04-08);
1158mysql>insert into student values ('1cg04is064','chandrika','ada', 1990-11-21);
1159mysql>insert into student values ('1cg04is105','Kumar','coding',1992-12-20);
1160mysql> insert into student values ('1cg04is110','Gagan','coding',1994-10-10);
1161mysql> select * from student;
1162+------------+-----------+----------+------------+
1163| regno | sname | major | bdate |
1164+------------+-----------+----------+------------+
1165| 1cg04is012 | asha | networks | 1986-06-07 |
1166| 1cg04is030 | anitha | fafl | 1984-04-08 |
1167| 1cg04is064 | chandrika | ada | 1990-11-21 |
1168| 1cg04is105 | Kumar | coding | 1992-12-20 |
1169| 1cg04is110 | Gangan | coding | 1994-10-10 |
1170+------------+-----------+----------+------------+
11715 rows in set (0.00 sec)
1172mysql>describe course;
1173+-------+-------------+------+-----+---------+-------+
1174| Field | Type | Null | Key | Default | Extra |
1175+-------+-------------+------+-----+---------+-------+
1176| cno | int(11) | NO | PRI | NULL | |
1177| cname | varchar(20) | YES | | NULL | |
1178| dept | varchar(20) | YES | | NULL | |
1179+-------+-------------+------+-----+---------+-------+
11803 rows in set (0.00 sec)
1181mysql>insert into course values (1,'network','ec');
1182mysql>insert into course values (2,'fafl','cs');
118325Database Applications Laboratory
11842016-17
1185mysql>insert into course values (3,'ada','cs');
1186mysql>insert into course values (4,'coding','ec');
1187mysql>insert into course values (5,'datastr','cs');
1188mysql>insert into course values (6,'testing','ec');
1189mysql>select * from course;
1190+-----+---------+------+
1191| cno | cname | dept |
1192+-----+---------+------+
1193| 1 | network | ec |
1194| 2 | fafl | cs |
1195| 3 | ada | cs |
1196| 4 | coding | ec |
1197| 5 | datastr | cs |
1198| 6 | testing | ec |
1199+-----+---------+------+
12006 rows in set (0.00 sec)
1201mysql>describe enroll;
1202+-------+-------------+------+-----+---------+-------+
1203| Field | Type | Null | Key | Default | Extra |
1204+-------+-------------+------+-----+---------+-------+
1205| regno | varchar(20) | NO | PRI | | |
1206| cno | int(11) | NO | PRI | 0 | |
1207| sem | int(11) | YES | | NULL | |
1208| marks | int(11) | YES | | NULL | |
1209+-------+-------------+------+-----+---------+-------+
12104 rows in set (0.01 sec)
1211mysql>insert into enroll values ('1cg04is012',1,6,85);
1212mysql>insert into enroll values ('1cg04is030',2,6,90);
1213mysql>insert into enroll values ('1cg04is064',3,6,92);
1214mysql>insert into enroll values ('1cg04is105',4,6,95);
1215mysql>insert into enroll values ('1cg04is110',5,4,90);
1216mysql>select * from enroll;
1217+------------+-----+------+-------+
1218| regno | cno | sem | marks |
1219+------------+-----+------+-------+
1220| 1cg04is012 | 1 | 6 | 85 |
1221| 1cg04is030 | 2 | 6 | 90 |
1222| 1cg04is064 | 3 | 6 | 92 |
1223| 1cg04is105 | 4 | 6 | 95 |
1224| 1cg04is110 | 5 | 4 | 90 |
1225+------------+-----+------+-------+
12265 rows in set (0.00 sec)
1227mysql>desc text;
1228+------------+-------------+------+-----+---------+-------+
1229| Field | Type | Null | Key | Default | Extra |
123026Database Applications Laboratory
12312016-17
1232+------------+-------------+------+-----+---------+-------+
1233| book_isbn | int(11) | NO | PRI | NULL | |
1234| book_title | varchar(20) | YES | | NULL | |
1235| pubisher | varchar(20) | YES | | NULL | |
1236| author | varchar(20) | YES | | NULL | |
1237+------------+-------------+------+-----+---------+-------+
12384 rows in set (0.00 sec)
1239mysql>insert into text values (111,'comp net','tmg','garcia');
1240mysql>insert into text values (222,'algo des','pearson','leviten');
1241mysql>insert into text values (333,'automata','pearson','ullman');
1242mysql>insert into text values (444,'data str','pearson','tbaum');
1243mysql>insert into text values (555,'usp','pearson','yash');
1244mysql>insert into text values (666,'algo2','pearson','leviten');
1245mysql>insert into text values (777,'networks','pearson','garcia');
1246mysql>insert into text values (888,'swtesting','sapnaǁ,'sapna');
1247mysql>select * from text;
1248+-----------+------------+----------+---------+
1249| book_isbn | book_title | pubisher | author |
1250+-----------+------------+----------+---------+
1251| 111 | comp net | tmg | garcia |
1252| 222 | algo des | pearson | leviten |
1253| 333 | automata | pearson | ullman |
1254| 444 | data str | pearson | tbaum |
1255| 555 | usp | pearson | yash |
1256| 666 | algo2 | pearson | leviten |
1257| 777 | networks | pearson | garcia |
1258| 888 | swtesting | sapna | sapna |
1259+-----------+------------+----------+---------+
12608 rows in set (0.00 sec)
1261mysql>desc book_adoption;
1262+-----------+---------+------+-----+---------+-------+
1263| Field | Type | Null | Key | Default | Extra |
1264+-----------+---------+------+-----+---------+-------+
1265| cno | int(11) | NO | PRI | 0 | |
1266| sem | int(11) | YES | | NULL | |
1267| book_isbn | int(11) | NO | PRI | 0 | |
1268+-----------+---------+------+-----+---------+-------+
12693 rows in set (0.01 sec)
1270mysql>insert into book_adoption values (01,6,111);
1271mysql>insert into book_adoption values (02,6,333);
1272mysql>insert into book_adoption values (02,6,666);
1273mysql>insert into book_adoption values (02,6,111);
1274mysql>insert into book_adoption values (03,6,222);
1275mysql>insert into book_adoption values (03,5,555);
1276mysql>insert into book_adoption values (04,6,555);
1277mysql>insert into book_adoption values (05,3,444);
1278mysql>insert into book_adoption values (01,6,666);
127927Database Applications Laboratory
12802016-17
1281mysql>insert into book_adoption values (01,6,777);
1282mysql>insert into book_adoption values (03,6, 777);
1283mysql>insert into book_adoption values (04,6, 777);
1284mysql>insert into book_adoption values (06,6, 888);
1285mysql>select * from book_adoption;
1286+-----+------+-----------+
1287| cno | sem | book_isbn |
1288+-----+------+-----------+
1289| 1 | 6 | 111 |
1290| 1 | 6 | 666 |
1291| 1 | 6 | 777 |
1292| 2 | 6 | 111 |
1293| 2 | 6 | 333 |
1294| 2 | 6 | 666 |
1295| 3 | 6 | 222 |
1296| 3 | 5 | 555 |
1297| 3 | 6 | 777 |
1298| 4 | 6 | 555 |
1299| 4 | 6 | 777 |
1300| 5 | 3 | 444 |
1301| 6 | 6 | 888 |
1302+-----+------+-----------+
130313 rows in set (0.00 sec)
13041: Demonstrate how you add a new text book to the database and make this book be adopted by some
1305department.
1306mysql>insert into text values ( ̳999‘,‘multimedia‘,‘phi‘,  ̳navathe‘);
1307mysql>insert into book_adoption values (6,3,999);
1308mysql>select * from text;
1309+-----------+------------+----------+---------+
1310| book_isbn | book_title | pubisher | author |
1311+-----------+------------+----------+---------+
1312| 111 | comp net | tmg | garcia |
1313| 222 | algo des | pearson | leviten |
1314| 333 | automata | pearson | ullman |
1315| 444 | data str | pearson | tbaum |
1316| 555 | usp | pearson | yash |
1317| 666 | algo2 | pearson | leviten |
1318| 777 | networks | pearson | garcia |
1319| 888 | swtesting | sapna | sapna |
1320| 999 | multimedia | phi | navathe |
1321+-----------+------------+----------+---------+
13229 rows in set (0.01 sec)
13232: Produce a list of text books (include Course#, Book-ISBN, Book-title) in the alphabetical order for
1324courses offered by the  ̳CS‘ department that use more than two books.
1325mysql>select c.cno, t.book_isbn, t.book_title
132628Database Applications Laboratory
13272016-17
1328from course c, book_adoption b, text t
1329where c.cno=b.cno and t.book_isbn=b.book_isbn and c.dept='cs'
1330and c.cno in ( select cno
1331from book_adoption
1332group by cno
1333having count(*)>2)
1334order by c.cno, t.book_title;
1335+-----+-----------+------------+
1336| cno | book_isbn | book_title |
1337+-----+-----------+------------+
1338| 2 | 666 | algo2 |
1339| 2 | 333 | automata |
1340| 2 | 111 | comp net |
1341| 3 | 222 | algo des |
1342| 3 | 777 | networks |
1343| 3 | 555 | usp |
1344+-----+-----------+------------+
13456 rows in set (0.00 sec)
1346[
13473: List any department that has all its adopted books published by a specific publisher.p
1348mysql>select distinct c.dept
1349from course c
1350where c.dept in
1351( select c.dept
1352from course c,book_adoption b,text t
1353where c.cno=b.cno
1354and t.book_isbn=b.book_isbn
1355and t.publisher='sapna')
1356and c.dept not in
1357(select c.dept
1358from course c,book_adoption b,text t
1359where c.cno=b.cno
1360and t.book_isbn=b.book_isbn
1361and t.publisher!='sapna');
1362+------+-----------------------------+
1363| dept | count(distinct b.book_isbn) |
1364+------+-----------------------------+
1365| ec | 6 |
1366+------+-----------------------------+
13671 row in set (0.00 sec)
136829Database Applications Laboratory
13692016-17
1370DATABASE 4 : Book_Dealer_Database
1371DESCRIPTION:
1372The following tables are maintained by a book dealer.
1373AUTHOR (author-id: int, name: string, city: string, country: string)
1374PUBLISHER (publisher-id: int, name: string, city: string, country: string)
1375CATALOG (book-id: int, title: string, author-id: int, publisher-id: int, category-id: int, year: int, price: int)
1376CATEGORY (category-id: int, description: string)
1377ORDER-DETAILS (order-no: int, book-id: int, quantity: int)
13781: Create the above tables by properly specifying the primary keys and foreign keys.
13792: Enter at least five tuples for each tables.
13803: Give the details of the authors who have 2 or more books in the catalog and the price of the books is
1381greater than the average price of the books in the catalog and the year of publication is after 2000.
13824: Find the author of the book which has maximum sales.
13835: Demonstrate how you increase the price of books published by a specific publisher by 10%.
13846: Generation of suitable reports.
1385mysql> create table author
1386(
1387authorid number(5),
1388name varchar(20),
1389city varchar(20),
1390country varchar(10),
1391primary key(authorid)
1392);
1393mysql> create table publisher
1394(
1395publisherid number(5),
1396name char(20),
1397city char(20),
1398country char(20),
1399primary key(publisherid)
1400);
1401mysql> create table catagory
1402(
1403catagoryid number(5),
1404description varchar(20),
1405primary key (catagoryid)
1406);
1407mysql> create table catalog
1408(
1409bookid number(5),
1410title varchar(20),
1411authorid number(5),
1412publisherid number(5),
1413catagoryid number(5),
141430Database Applications Laboratory
14152016-17
1416year number(5),
1417price number(4),
1418primary key(bookid),
1419foreign key (authorid) references author(authorid),
1420foreign key (publisherid) references publisher (publisherid),
1421foreign key (catagoryid) references catagory(catagoryid)
1422);
1423mysql> create table order_details
1424(
1425ono int primary key,
1426bookid int,
1427quantity int,
1428foreign key (bookid) references catalog(bookid)
1429);
1430mysql>desc author;
1431+----------+-------------+------+-----+---------+-------+
1432| Field | Type | Null Key | Default | Extra |
1433+----------+-------------+------+-----+---------+-------+
1434| authorid | int(11) | NO | PRI | 0 | |
1435| name | varchar(20) | YES | | NULL | |
1436| city | varchar(20) | YES | | NULL | |
1437| country | varchar(10) | YES | | NULL | |
1438+----------+-------------+------+-----+---------+-------+
14394 rows in set (0.00 sec)
1440mysql> insert into author values(1,'harish','delhi','india');
1441mysql> insert into author values(2,'balu','bangalore','india');
1442mysql> insert into author values(3,'gopal','goa','india');
1443mysql> insert into author values(4,'sagar','kerala','india');
1444mysql> insert into author values(5,'trivedi','washington','usa');
1445mysql> select * from author;
1446+----------+---------+------------+---------+
1447| authorid | name | city | country |
1448+----------+---------+------------+---------+
1449| 1 | harish | delhi | india |
1450| 2 | balu | bangalore | india |
1451| 3 | gopal | goa | india |
1452| 4 | sagar | kerala | india |
1453| 5 | trivedi | washington | usa |
1454+----------+---------+------------+---------+
14555 rows in set (0.00 sec)
1456mysql> desc publisher;
1457+-------------+----------+------+-----+---------+-------+
1458| Field | Type | Null | Key | Default | Extra |
1459+-------------+----------+------+-----+---------+-------+
1460| publisherid | int(11) | NO | PRI | 0 | |
1461| name | char(20) | YES | | NULL | |
146231Database Applications Laboratory
14632016-17
1464| city | char(20) | YES | | NULL | |
1465| country | char(20) | YES | | NULL | |
1466+-------------+----------+------+-----+---------+-------+
14674 rows in set (0.00 sec)
1468mysql> insert into publisher values(10,'pearson','goa','india');
1469mysql> insert into publisher values(11,'sapna','kerala','india');
1470mysql> insert into publisher values(12,'sapna','bangalore','india');
1471mysql> insert into publisher values(13,'sagar','kolkata','india');
1472mysql> insert into publisher values(14,'tata','washington','usa');
1473mysql> select * from publisher;
1474+-------------+---------+------------+---------+
1475| publisherid | name | city | country |
1476+-------------+---------+------------+---------+
1477| 10 | pearson | goa | india |
1478| 11 | sapna | kerala | india |
1479| 12 | sapna | bangalore | india |
1480| 13 | sagar | kolkata | india |
1481| 14 | tata | washington | usa |
1482+-------------+---------+------------+---------+
14835 rows in set (0.01 sec)
1484mysql> desc catagory;
1485+-------------+-------------+------+-----+---------+-------+
1486| Field | Type | Null | Key | Default | Extra |
1487+-------------+-------------+------+-----+---------+-------+
1488| catagoryid | int(11) | NO | PRI | 0 | |
1489| description | varchar(20) | YES | | NULL | |
1490+-------------+-------------+------+-----+---------+-------+
14912 rows in set (0.00 sec)
1492mysql> insert into catagory values(111,'moral');
1493mysql> insert into catagory values(222,'novels');
1494mysql> insert into catagory values(333,'journals');
1495mysql> insert into catagory values(444,'technical');
1496mysql> insert into catagory values(555,'story');
1497mysql> select * from catagory;
1498+------------+-------------+
1499| catagoryid | description |
1500+------------+-------------+
1501| 111 | moral |
1502| 222 | novels |
1503| 333 | journals |
1504| 444 | technical |
1505| 555 | story |
1506+------------+-------------+
15075 rows in set (0.01 sec)
1508mysql> desc catalog;
1509+-------------+-------------+------+-----+---------+-------+
1510| Field | Type | Null | Key | Default | Extra |
1511+-------------+-------------+------+-----+---------+-------+
151232Database Applications Laboratory
15132016-17
1514| bookid | int(11) | NO | PRI | 0 | |
1515| title | varchar(20) | YES | | NULL | |
1516| authorid | int(11) | YES | MUL | NULL | |
1517| publisherid | int(11) | YES | MUL | NULL | |
1518| catagoryid | int(11) | YES | MUL | NULL | |
1519| year | int(11) | YES | | NULL | |
1520| price | int(11) | YES | | NULL | |
1521+-------------+-------------+------+-----+---------+-------+
15227 rows in set (0.00 sec)
1523mysql> insert into catalog values(20,'computer science',1,11,111,2006,290);
1524mysql> insert into catalog values(21,'operating system',5,10,222,2002,220);
1525mysql> insert into catalog values(22,'dbms',2,11,555,2006,250);
1526mysql> insert into catalog values(23,'unix',1,12,222,2002,350);
1527mysql> insert into catalog values(24,'ada',2,11,444,2005,450);
1528mysql> insert into catalog values(25,'cn2',1,11,444,2005,450);
1529mysql> select * from catalog;
1530+--------+------------------+----------+-------------+------------+------+-------+
1531| bookid | title | authorid | publisherid | catagoryid | year | price |
1532+--------+------------------+----------+-------------+------------+------+-------+
1533| 20 | computer science | 1 | 11 | 111 | 2006 | 290 |
1534| 21 | operating system | 5 | 10 | 222 | 2002 | 220 |
1535| 22 | dbms | 2 | 11 | 555 | 2006 | 250 |
1536| 23 | unix | 1 | 12 | 222 | 2002 | 350 |
1537| 24 | ada | 2 | 11 | 444 | 2005 | 450 |
1538| 25 | cn2 | 1 | 11 | 444 | 2005 | 450 |
1539+--------+------------------+----------+-------------+------------+------+-------+
15406 rows in set (0.00 sec)
1541mysql> desc order_details;
1542+----------+---------+------+-----+---------+-------+
1543| Field | Type | Null | Key | Default | Extra |
1544+----------+---------+------+-----+---------+-------+
1545| ono | int(11) | NO | PRI | NULL | |
1546| bookid | int(11) | YES | MUL | NULL | |
1547| quantity | int(11) | YES | | NULL | |
1548+----------+---------+------+-----+---------+-------+
15493 rows in set (0.00 sec)
1550mysql> insert into order_details values(50,21,100);
1551mysql> insert into order_details values(51,22,200);
1552mysql> insert into order_details values(52,24,250);
1553mysql> insert into order_details values(53,21,150);
1554mysql> insert into order_details values(54,24,350);
1555mysql> select * from order_details;
1556+-----+--------+----------+
1557| ono | bookid | quantity |
1558+-----+--------+----------+
1559| 50 | 21 | 100 |
1560| 51 | 22 | 200 |
1561| 52 | 24 | 250 |
156233Database Applications Laboratory
15632016-17
1564| 53 | 21 | 150 |
1565| 54 | 24 | 350 |
1566+-----+--------+----------+
15675 rows in set (0.00 sec)
15681: Give the details of the authors who have 2 or more books in the catalog and the price of the books is
1569greater than the average price of the books in the catalog and the year of publication is after 2000.
1570mysql>select c.authorid, a.name, a.city, a.country
1571from author a, catalog c
1572where a.authorid=c.authorid and c.year>2000
1573group by c.authorid having count(a.authorid)>=2 and
1574sum(price)>(select avg(price) from catalog);
1575+----------+--------+-----------+---------+
1576| authorid | name | city | country |
1577+----------+--------+-----------+---------+
1578| 1 | harish | delhi | india |
1579| 2 | balu | bangalore | india |
1580+----------+--------+-----------+---------+
15812 rows in set (0.00 sec)
15822: Find the author of the book which has maximum sales.
1583mysql> create view SUM_OF_QTY as (select bookid, sum(quantity) as SUM from order_details group
1584by bookid);
1585Query OK, 0 rows affected (0.06 sec)
1586mysql> select * from SUM_OF_QTY;
1587+--------+------+
1588| bookid | SUM |
1589+--------+------+
1590| 21 | 250 |
1591| 22 | 200 |
1592| 24 | 600 |
1593+--------+------+
15943 rows in set (0.00 sec)
1595mysql> select a.name,o.bookid from author a, catalog c, order_details o
1596where a.authorid=c.authorid and c.bookid=o.bookid
1597group by a.name,o.bookid having sum(quantity) >= ( select max(SUM) from SUM_OF_QTY);
1598+------+--------+
1599| name | bookid |
1600+------+--------+
1601| balu | 24 |
1602+------+--------+
16031 row in set (0.01 sec)
16043: Demonstrate how you increase the price of books published by a specific publisher by 10%.
1605update catalog
1606set price=price+price price*0.1
1607where publisherid in(select publisherid from publisher where name=‘pearson‘);
1608Before update
160934Database Applications Laboratory
16102016-17
1611mysql> select * from catalog;
1612+--------+------------------+----------+-------------+------------+------+-------+
1613| bookid | title | authorid | publisherid | catagoryid | year | price |
1614+--------+------------------+----------+-------------+------------+------+-------+
1615| 20 | computer science | 1 | 11 | 111 | 2006 | 290 |
1616| 21 | operating system | 5 | 10 | 222 | 2002 | 220 |
1617| 22 | dbms | 2 | 11 | 555 | 2006 | 250 |
1618| 23 | unix | 1 | 12 | 222 | 2002 | 350 |
1619| 24 | ada | 2 | 11 | 444 | 2005 | 450 |
1620| 25 | cn2 | 1 | 11 | 444 | 2005 | 450 |
1621+--------+------------------+----------+-------------+------------+------+-------+
16226 rows in set (0.00 sec)
1623After update
1624+--------+------------------+----------+-------------+------------+------+-------+
1625| bookid | title | authorid | publisherid | catagoryid | year | price |
1626+--------+------------------+----------+-------------+------------+------+-------+*
1627| 20 | computer science | 1 | 11 | 111 | 2006 | 290 |
1628| 21 | operating system | 5 | 10 | 222 | 2002 | 242 |
1629| 22 | dbms | 2 | 11 | 555 | 2006 | 250 |
1630| 23 | unix | 1 | 12 | 222 | 2002 | 350 |
1631| 24 | ada | 2 | 11 | 444 | 2005 | 450 |
1632| 25 | cn2 | 1 | 11 | 444 | 2005 | 450 |
1633+--------+------------------+----------+-------------+------------+------+-------+
16346 rows in set (0.00 sec)
163535Database Applications Laboratory
16362016-17
1637DATABASE 5 : Banking_Enterprise_database
1638DESCRIPTION:
1639Consider the following database for a banking enterprise
1640BRANCH(branch-name:string, branch-city:string, assets:real)
1641ACCOUNT(accno:int, branch-name:string, balance:real)
1642DEPOSITOR(customer-name:string, accno:int)
1643CUSTOMER(customer-name:string, customer-street:string, customer-city:string)
1644LOAN(loan-number:int, branch-name:string, amount:real)
1645BORROWER(customer-name:string, loan-number:int)
1646i Create the above tables by properly specifying the primary keys and the foreign keys
1647ii. Enter at least five tuples for each relation
1648iii. Find all the customers who have at least two accounts at the Main branch.
1649iv. Find all the customers who have an account at all the branches located in a specific city.
1650v. Demonstrate how you delete all account tuples at every branch located in a specific city.
1651vi. Generate suitable reports.
1652vii Create suitable front end for querying and displaying the results.
1653mysql> create table branch
1654(
1655branchname varchar(20) primary key,
1656branchcity varchar(20),
1657assets int
1658);
1659mysql> create table account
1660(
1661accno int primary key,
1662branchname varchar(20),
1663balance real,
1664foreign key (branchname) references branch (branchname)
1665);
1666mysql> create table customer
1667(
1668customername varchar(20) primary key,
1669customerstreet varchar(20),
1670customercity varchar(20)
1671);
1672mysql> create table depositor
1673(
1674customername varchar(20),
1675accno int,
1676primary key(customername,accno),
1677foreign key(customername) references customer(customername),
1678foreign key(accno) references account(accno)on delete cascade
1679);
168036Database Applications Laboratory
16812016-17
1682mysql> create table loan
1683(
1684loanno int,
1685branchname varchar(20),
1686amount real,
1687primary key(loanno),
1688foreign key(branchname) references branch(branchname)
1689);
1690mysql> create table borrower
1691(
1692customername varchar(20),
1693loanno int,
1694primary key(customername,loanno),
1695foreign key(customername) references customer(customername),
1696foreign key(loanno) references loan(loanno)
1697);
1698mysql> desc branch;
1699+------------+-------------+------+-----+---------+-------+
1700| Field | Type | Null | Key | Default | Extra |
1701+------------+-------------+------+-----+---------+-------+
1702| branchname | varchar(20) | NO | PRI | NULL | |
1703| branchcity | varchar(20) | YES | | NULL | |
1704| assets | int(11) | YES | | NULL | |
1705+------------+-------------+------+-----+---------+-------+
17063 rows in set (0.00 sec)
1707mysql>insert into branch values( ―basavanagudiǁ,ǁbangaloreǁ,25000000);
1708mysql>insert into branch values( ―greenparkǁ,ǁNewDelhiǁ, 50000000);
1709mysql>insert into branch values( ―jayanagarǁ,ǁ bangaloreǁ, 50000000);
1710mysql>insert into branch values( ―marinadriveǁ,ǁ mumbaiǁ, 55000000);
1711mysql>insert into branch values( ―Noidaǁ,ǁ NewDelhiǁ, 35000000);
1712mysql> select * from branch;
1713+--------------+------------+----------+
1714| branchname | branchcity | assets |
1715+--------------+------------+----------+
1716| basavanagudi | bangalore | 25000000 |
1717| greenpark | NewDelhi | 50000000 |
1718| jayanagar | bangalore | 50000000 |
1719| marinadrive | mumbai | 55000000 |
1720| Noida | NewDelhi | 35000000 |
1721+--------------+------------+----------+
17225 rows in set (0.00 sec)
1723mysql> desc account;
1724+------------+-------------+------+-----+---------+-------+
1725| Field | Type | Null | Key | Default | Extra |
1726+------------+---- ---------+------+-----+---------+-------+
1727| accno | int(11) | NO | PRI | NULL | |
172837Database Applications Laboratory
17292016-17
1730| branchname | varchar(20) | YES | MUL | NULL | |
1731| balance | double | YES | | NULL | |
1732+------------+-------------+------+-----+---------+-------+
17333 rows in set (0.00 sec)
1734mysql>insert into account values( 105,ǁ jayanagarǁ,45000);
1735mysql>insert into account values( 205,ǁ jayanagarǁ,50000);
1736mysql>insert into account values( 250,ǁ basavanagudiǁ,40000);
1737mysql>insert into account values( 450,ǁ basavanagudiǁ,20000);
1738mysql>insert into account values( 458,ǁ noidaǁ,25000);
1739mysql>insert into account values( 780,ǁ greenparkǁ,40000);
1740mysql>insert into account values( 900,ǁ marinadriveǁ,100000);
1741mysql> select * from account;
1742+-------+--------------+---------+
1743| accno | branchname | balance |
1744+-------+--------------+---------+
1745| 105 | jayanagar | 45000 |
1746| 205 | jayanagar | 50000 |
1747| 250 | basavanagudi | 40000 |
1748| 450 | basavanagudi | 20000 |
1749| 458 | noida | 25000 |
1750| 780 | greenpark | 40000 |
1751| 900 | marinadrive | 100000 |
1752+-------+--------------+---------+
17537 rows in set (0.00 sec)
1754mysql> desc loan;
1755+------------+-------------+------+-----+---------+-------+
1756| Field | Type | Null | Key | Default | Extra |
1757+------------+-------------+------+-----+---------+-------+
1758| loanno | int(11) | NO | PRI | 0 | |
1759| branchname | varchar(20) | YES | MUL | NULL | |
1760| amount | double | YES | | NULL | |
1761+------------+-------------+------+-----+---------+-------+
17623 rows in set (0.00 sec)
1763mysql>insert into loan values( 1002,ǁ jayanagar ǁ,255000);
1764mysql>insert into loan values( 1003,ǁ basavanagudiǁ,555000);
1765mysql>insert into loan values( 1004,ǁ noidaǁ,500000);
1766mysql>insert into loan values( 1005,ǁ marinadriveǁ,400000);
1767mysql>insert into loan values( 1006,ǁ greenparkǁ,600000);
1768mysql> select * from loan;
1769+--------+--------------+--------+
1770| loanno | branchname | amount |
1771+--------+--------------+--------+
1772| 1002 | jayanagar | 255000 |
1773| 1003 | basavanagudi | 555000 |
1774| 1004 | noida | 500000 |
1775| 1005 | marinadrive | 400000 |
177638Database Applications Laboratory
17772016-17
1778| 1006 | greenpark | 600000 |
1779+--------+--------------+--------+
17805 rows in set (0.00 sec)
1781mysql> desc customer;
1782+----------------+-------------+------+-----+---------+-------+
1783| Field | Type | Null | Key | Default | Extra |
1784+----------------+-------------+------+-----+---------+-------+
1785| customername | varchar(20) | NO | PRI | NULL | |
1786| customerstreet | varchar(20) | YES | | NULL | |
1787| customercity | varchar(20) | YES | | NULL | |
1788+----------------+-------------+------+-----+---------+-------+
17893 rows in set (0.00 sec)
1790mysql>insert into customer values( ―johnǁ,ǁnoidaǁ,ǁnewdelhiǁ);
1791mysql>insert into customer values( ―kumarǁ,ǁbasavanagudiǁ,ǁbangaloreǁ);
1792mysql>insert into customer values( ―rajuǁ,ǁjayanagarǁ,ǁbangaloreǁ);
1793mysql>insert into customer values( ―smithǁ,ǁhighhillsǁ,ǁnewdelhiǁ);
1794mysql>insert into customer values( ―sujalǁ,ǁkuvempuǁ,ǁbangaloreǁ);
1795mysql> select * from customer;
1796+--------------+----------------+--------------+
1797| customername | customerstreet | customercity |
1798+--------------+----------------+--------------+
1799| john | noida | newdelhi |
1800| kumar | basavanagudi | bangalore |
1801| raju | jayanagar | bangalore |
1802| smith | highhills | newdelhi |
1803| sujal | kuvempu | bangalore |
1804+--------------+----------------+--------------+
1805mysql> desc borrower;
1806+--------------+-------------+------+-----+---------+-------+
1807| Field | Type | Null | Key | Default | Extra |
1808+--------------+-------------+------+-----+---------+-------+
1809| customername | varchar(20) | NO | PRI | | |
1810| loanno | int(11) | NO | PRI | 0 | |
1811+--------------+-------------+------+-----+---------+-------+
18122 rows in set (0.00 sec)
1813mysql>insert into borrower values( ―johnǁ,1002);
1814mysql>insert into borrower values( ―kumarǁ,1003);
1815mysql>insert into borrower values( ―rajuǁ,1004);
1816mysql>insert into borrower values( ―smithǁ,1005);
1817mysql>insert into borrower values( ―sujalǁ,1006);
1818mysql> select * from borrower;
1819+--------------+--------+
1820| customername | loanno |
1821+--------------+--------+
1822| john | 1002 |
182339Database Applications Laboratory
18242016-17
1825| kumar| 1003 |
1826raju | 1004 |
1827| smith | 1005 |
1828| sujal | 1006 |
1829+--------------+--------+
18305 rows in set (0.00 sec)
18311: Find all the customers who have at least 2 accounts at the main branch.
1832mysql> select d.customername
1833from depositor d, account a
1834where d.accno=a.accno
1835group by d.customername having count(d.customername)>=2;
1836+--------------+
1837| customername |
1838+--------------+
1839| john |
1840+--------------+
18411 row in set (0.00 sec)
18422: Find all the customers who have an account at all the branches located in a specific city.
1843mysql> select d.customername , count(distinct b.branchname)
1844from account a, depositor d, branch b
1845where a.accno=d.accno and b.branchname =a.branchname and b.branchcity=ǁbangaloreǁ
1846group by d.customername having count(distinct b.branchname) = (select count(distinct b.branchname)
1847from branch b where b.branchcity=ǁbangaloreǁ);
1848+--------------+------------------------------+
1849| customername | count(distinct b.branchname) |
1850+--------------+------------------------------+
1851| john | 2 |
1852+--------------+------------------------------+
18531 row in set (0.00 sec)
18543: Demonstrate how you delete all account tuples at every branch located in a specific city.
1855mysql> delete from account where bname in ( select bname from branch where bcity=‘mumbai‘);
1856mysql> select * from account;
1857+-------+--------------+---------+
1858| accno | branchname | balance |
1859+-------+--------------+---------+
1860| 105 | jayanagar | 45000 |
1861| 205 | jayanagar | 50000 |
1862| 250 | basavanagudi | 40000 |
1863| 450 | basavanagudi | 20000 |
1864| 458 | noida | 25000 |
1865| 780 | greenpark | 40000 |
1866+-------+--------------+---------+
18676 rows in set (0.00 sec)
1868mysql> select * from depositor;+--------------+-------+
186940Database Applications Laboratory
18702016-17
1871| customername | accno |
1872+--------------+-------+
1873| john | 105 |
1874| john | 205 |
1875| john | 250 |
1876| kumar | 450 |
1877| raju | 458 |
1878| smith | 780 |
1879+--------------+-------+
18806 rows in set (0.00 sec)
188141Database Applications Laboratory
18822016-17
1883Viva voice questions
18841. What is database?
1885A database is a logically coherent collection of data with some inherent meaning, representing
1886some aspect of real world and which is designed, built and populated with data for a specific
1887purpose.
18882. What is DBMS?
1889It is a collection of programs that enables user to create and maintain a database. In other words
1890it is general-purpose software that provides the users with the processes of defining, constructing
1891and manipulating the database for various applications.
18923. What is a Database system?
1893The database and DBMS software together is called as Database system.
18944. Advantages of DBMS?
1895Redundancy is controlled.
1896Unauthorized access is restricted.
1897Providing multiple user interfaces.
1898Enforcing integrity constraints.
1899Providing backup and recovery.
19005. Disadvantage in File Processing System?
1901Data redundancy & inconsistency.
1902Difficult in accessing data.
1903Data isolation.
1904Data integrity.
1905Concurrent access is not possible.
1906Security Problems.
19076. Describe the three levels of data abstraction?
190842Database Applications Laboratory
19092016-17
1910Three levels of abstraction:
1911Physical level: The lowest level of abstraction describes how data are stored.
1912Logical level: The next higher level of abstraction, describes what data are stored in database
1913and what relationship among those data.
1914View level: The highest level of abstraction describes only part of entire database.
19157. Define the "integrity rules"
1916There are two Integrity rules.
1917Entity Integrity: States that? Primary key cannot have NULL value?
1918Referential Integrity: States that? Foreign Key can be either a NULL value or should be Primary
1919Key value of other relation.
19208. What is extension and intension?
1921Extension -It is the number of tuples present in a table at any instance. This is time dependent.
1922Intension -It is a constant value that gives the name, structure of table and the constraints laid on
1923it.
19249. What is System R? What are its two major subsystems?
1925System R was designed and developed over a period of 1974-79 at IBM San Jose Research
1926Center. It is a prototype and its purpose was to demonstrate that it is possible to build a
1927Relational System that can be used in a real life environment to solve real life problems, with
1928performance at least comparable to that of existing system.
1929Its two subsystems are
1930Research Storage
1931System Relational Data System.
193210. How is the data structure of System R different from the relational structure?
1933Unlike Relational systems in System R
1934Domains are not supported
1935Enforcement of candidate key uniqueness is optional
1936Enforcement of entity integrity is optional
1937Referential integrity is not enforced
193843Database Applications Laboratory
19392016-17
194011. What is Data Independence?
1941Data independence means that? The application is independent of the storage structure and
1942access strategy of data? In other words, the ability to modify the schema definition in one level
1943should not affect the schema definition in the next higher level.
1944Two types of Data Independence:
1945Physical Data Independence: Modification in physical level should not affect the logical level.
1946Logical Data Independence: Modification in logical level should affect the view level.
1947NOTE: Logical Data Independence is more difficult to achieve
194812. What is a view? How it is related to data independence?
1949A view may be thought of as a virtual table, that is, a table that does not really exist in its own
1950right but is instead derived from one or more underlying base table. In other words, there is no
1951stored file that direct represents the view instead a definition of view is stored in data dictionary.
1952Growth and restructuring of base tables is not reflected in views. Thus the view can insulate
1953users from the effects of restructuring and growth in the database. Hence accounts for logical
1954data independence.
195513. What is Data Model?
1956A collection of conceptual tools for describing data, data relationships data semantics and
1957constraints.
195814. What is E-R model?
1959This data model is based on real world that consists of basic objects called entities and of
1960relationship among these objects. Entities are described in a database by a set of attributes.
196115. What is Object Oriented model?
1962This model is based on collection of objects. An object contains values stored in instance
1963variables with in the object. An object also contains bodies of code that operate on the object.
1964These bodies of code are called methods. Objects that contain same types of values and the same
1965methods are grouped together into classes.
196616. What is an Entity?
1967It is a 'thing' in the real world with an independent existence.
196844Database Applications Laboratory
19692016-17
197017. What is an Entity type?
1971It is a collection (set) of entities that have same attributes.
197218. What is an Entity set?
1973It is a collection of all entities of particular entity type in the database.
197419. What is an Extension of entity type?
1975The collections of entities of a particular entity type are grouped together into an entity set.
197620. What is Weak Entity set?
1977An entity set may not have sufficient attributes to form a primary key, and its primary key
1978compromises of its partial key and primary key of its parent entity, then it is said to be Weak
1979Entity set.
198021. What is an attribute?
1981It is a particular property, which describes the entity.
198222. What is a Relation Schema and a Relation?
1983A relation Schema denoted by R (A1, A2,...?, An) is made up of the relation name R and the list
1984of attributes Ai that it contains. A relation is defined as a set of tuples. Let r be the relation which
1985contains set tuples (t1, t2, t3... tn). Each tuple is an ordered list of n-values t= (v1, v2... vn).
198623. What is degree of a Relation?
1987It is the number of attribute of its relation schema.
198824. What is Relationship?
1989It is an association among two or more entities.
199025. What is Relationship set?
1991The collection (or set) of similar relationships.
199245Database Applications Laboratory
19932016-17
199426. What is Relationship type?
1995Relationship type defines a set of associations or a relationship set among a given set of entity
1996types.
199727. What is degree of Relationship type?
1998It is the number of entity type participating.
199925. What is DDL (Data Definition Language)?
2000A data base schema is specifies by a set of definitions expressed by a special language called
2001DDL.
200226. What is VDL (View Definition Language)?
2003It specifies user views and their mappings to the conceptual schema.
200427. What is SDL (Storage Definition Language)?
2005This language is to specify the internal schema. This language may specify the mapping between
2006two schemas.
200728. What is Data Storage - Definition Language?
2008The storage structures and access methods used by database system are specified by a set of
2009definition in a special type of DDL called data storage-definition language.
201029. What is DML (Data Manipulation Language)?
2011This language that enable user to access or manipulate data as organized by appropriate data
2012model.
2013Procedural DML or Low level: DML requires a user to specify what data are needed and how to
2014get those data.
2015? Non-Procedural DML or High level: DML requires a user to specify what data are needed
2016without specifying how to get those data.
201731. What is DML Compiler?
2018It translates DML statements in a query language into low-level instruction that the query
2019evaluation engine can understand.
202046Database Applications Laboratory
20212016-17
202232. What is Query evaluation engine?
2023It executes low-level instruction generated by compiler.
202433. What is DDL Interpreter?
2025It interprets DDL statements and record them in tables containing metadata.
202634. What is Record-at-a-time?
2027The Low level or Procedural DML can specify and retrieve each record from a set of records.
2028This retrieve of a record is said to be Record-at-a-time.
202935. What is Set-at-a-time or Set-oriented?
2030The High level or Non-procedural DML can specify and retrieve many records in a single DML
2031statement. This retrieve of a record is said to be Set-at-a-time or Set-oriented.
203236. What is Relational Algebra?
2033It is procedural query language. It consists of a set of operations that take one or two relations as
2034input and produce a new relation.
203537. What is Relational Calculus?
2036It is an applied predicate calculus specifically tailored for relational databases proposed by E.F.
2037Codd. E.g. of languages based on it are DSL ALPHA, QUEL.
203838. How does Tuple-oriented relational calculus differ from domain-oriented relational calculus
2039The tuple-oriented calculus uses a tuple variables i.e., variable whose only permitted values are
2040tuples of that relation. E.g. QUEL
2041The domain-oriented calculus has domain variables i.e., variables that range over the underlying
2042domains instead of over relation. E.g. ILL, DEDUCE.
204339. What is normalization?
2044It is a process of analysing the given relation schemas based on their Functional Dependencies
2045(FDs) and primary key to achieve the properties
2046Minimizing redundancy
204747Database Applications Laboratory
20482016-17
2049Minimizing insertion, deletion and update anomalies.
205040. What is Functional Dependency?
2051A Functional dependency is denoted by X Y between two sets of attributes X and Y that are
2052subsets of R specifies a constraint on the possible tuple that can form a relation state r of R. The
2053constraint is for any two tuples t1 and t2 in r if t1[X] = t2[X] then they have t1[Y] = t2[Y]. This
2054means the value of X component of a tuple uniquely determines the value of component Y.
205541. When is a functional dependency F said to be minimal?
2056Every dependency in F has a single attribute for its right hand side.
2057We cannot replace any dependency X A in F with a dependency Y A where Y is a proper subset
2058of X and still have a set of dependency that is equivalent to F.
2059We cannot remove any dependency from F and still have set of dependency that is equivalent to
2060F.
206142. What is multivalued dependency?
2062Multivalued dependency denoted by X Y specified on relation schema R, where X and Y are
2063both subsets of R, specifies the following constraint on any relation r of R: if two tuples t1 and t2
2064exist in r such that t1[X] = t2[X] then t3 and t4 should also exist in r with the following
2065properties
2066t3[x] = t4[X] = t1[X] = t2[X]
2067t3[Y] = t1[Y] and t4[Y] = t2[Y]
2068t3 [Z] = t2[Z] and t4[Z] = t1[Z]
2069Where [Z = (R-(X U Y)) ]
207043. What is Lossless join property?
2071It guarantees that the spurious tuples generation does not occur with respect to relation schemas
2072after decomposition.
207344. What is 1 NF (Normal Form)?
2074The domain of attribute must include only atomic (simple, indivisible) values.
207545. What is Fully Functional dependency?
207648Database Applications Laboratory
20772016-17
2078It is based on concept of full functional dependency. A functional dependency X Y is full
2079functional dependency if removal of any attribute A from X means that the dependency does not
2080hold any more.
208146. What is 2NF?
2082A relation schema R is in 2NF if it is in 1NF and every non-prime attribute A in R is fully
2083functionally dependent on primary key.
208447. What is 3NF?
2085A relation schema R is in 3NF if it is in 2NF and for every FD X A either of the following is true
2086X is a Super-key of R.
2087A is a prime attribute of R.
2088In other words, if every non prime attribute is non-transitively dependent on primary key.
208948. What is BCNF (Boyce-Codd Normal Form)?
2090A relation schema R is in BCNF if it is in 3NF and satisfies an additional constraint that for
2091every FD X A, X must be a candidate key.
209249. What is 4NF?
2093A relation schema R is said to be in 4NF if for every multivalued dependency X Y that holds
2094over R, one of following is true
2095X is subset or equal to (or) XY = R.
2096X is a super key.
209750. What is 5NF?
2098A Relation schema R is said to be 5NF if for every join dependency {R1, R2... Rn} that holds R,
2099one the following is true
2100Ri = R for some i.
2101The join dependency is implied by the set of FD, over R in which the left side is key of R.
210249Database Applications Laboratory
21032016-17
2104Database Applications – DBMS
2105Applications where we use Database Management Systems are:
2106ï‚·
2107ï‚·
2108ï‚·
2109ï‚·
2110ï‚·
2111Telecom: There is a database to keeps track of the information regarding calls made, network
2112usage, customer details etc. Without the database systems it is hard to maintain that huge amount
2113of data that keeps updating every millisecond.
2114Industry: Where it is a manufacturing unit, warehouse or distribution centre, each one needs a
2115database to keep the records of ins and outs. For example distribution centre should keep a track
2116of the product units that supplied into the centre as well as the products that got delivered out
2117from the distribution centre on each day; this is where DBMS comes into picture.
2118Banking System: For storing customer info, tracking day to day credit and debit transactions,
2119generating bank statements etc. All this work has been done with the help of Database
2120management systems.
2121Education sector: Database systems are frequently used in schools and colleges to store and
2122retrieve the data regarding student details, staff details, course details, exam details, payroll data,
2123attendance details, fees details etc. There is a hell lot amount of inter-related data that needs to be
2124stored and retrieved in an efficient manner.
2125Online shopping: You must be aware of the online shopping websites such as Amazon, Flipkart
2126etc. These sites store the product information, your addresses and preferences, credit details and
2127provide you the relevant list of products based on your query. All this involves a Database
2128management system.
2129The overall purpose of DBMS is to allow the users to define, store, retrieve and update the
2130information contained in the database on demand. Information can be anything that is of
2131significance to an individual or organization.
213250