TOPIC : MYSQL : REVISION TOUR, MORE ON SQL

Q1. What is DBMS? What is the function of database management system ?

Q2. What is Data Model? Name various types of Data Model?
Q3. What is data redundancy? What are the problems associated with it?
Q4. Illustrate the difference between the three levels of data abstraction.
Q5. What is meant by “data independence”? What are types of Data Independence?
Q6. What is SQL?
Q7. Define various Relational Model Terminology
1. Relation 2. Tuple 3. Attribute 4. Degree
5. Cardinality 6. Primary Key 7. Candidate Key 8. Alternate Key
9. Foreign Key
Q8 What are various Integrity Constraints? Describe them?
Q9 How are SQL Statements Classified?

Q10.Create the following table

TABLE ‐ DEPT
Column Name Type SIZE Constraint Description
DEPTNO INTEGER PRIMARY KEY DEPARTMENT NUMBER
DNAME VARCHAR 20 NAME OF DEPARTMENT
LOC VARCHAR 10 LOCATION OF DEPARTMENT

TABLE ‐ EMP
Column Name Type SIZE Constraint Description
EMPNO INTEGER PRIMARY KEY EMPLOYEE NUMBER
ENAME VARCHAR 20 NOT NULL EMPLOYEE NAME
JOB CHAR 10 DESIGNATION
MGR INTEGER RESPECTIVE MANGER’S EMPNO
HIREDATE DATE DATE OF JOINING
SAL DECIMAL 9,2 >0 SALARY
COMM INTEGER COMMISSION
DEPTNO INTEGER FOREIGN KEY DEPARTMENT NUMBER
DEPT DEPTNO

Q11 On the basis of above table perform the following queries

a) List the employee belonging to the department 20 and working as salesman
b) List the employee number and name of mangers.
c) List the name of clerks working in department 20
d) List the details of the employees who have joined before the end of September 2014
e) List the names of employees who are not mangers.
f) List the name of employees whose employees numbers are 7369,7521,7839,7934,7788
g) List the employee name and salary whose salary is between 1000 and 2000.
h) List the employee name how have joined before 30 June 2014 and after Dec 2014
i) List the different job available in the emp table
j) List the employee who are not eligible for commission.
k) List the employee whose name start with “S”
l) List the name of employee whose name has 5 charcters.
 m) List the name of employee having ‘I” as second character.
n) List the empno, name and salary in ascending order of salary.
o) List the employee name and hiredate in descending order of hiredate.
p) List the employee name, salary, pf, hra, da and gross; order the result in ascending order of
gross. Pf is 10% of salary, HRA is 50% of salary and da is 30% of salary.
q) List the number of employees working in emp table.
r) List the number of jobs available in emp table.
s) List the department number and the total salary payable in each department.
t) List the job and the number of employees in each job. The result should be in descending order
of the number of employees.
u) List the total salary, maximum and minimum salary and the average salary of employees
jobwise for department number 20 only.
v) List the average monthly salary for each job type within department.
w) List the average salary for all department employing more than 5 people.
x) List the total salary, maximum and minimum salary and the average salary of employee job
wise for department number 20 and display only those rows having average salary greater
than 1000 in ascending order of sum(sal).
y) List the employee number, name and department number and department name of clerks.
z) Display the total salary which is sum of salary and commission.
aa) Add a column address to the employee table
bb) Suppose the user forget to make empno as primary key and deptno as foreign key write the
query to make such changes.
cc) Increase the size of salary from 9,2 to 15,2
dd) Drop the column address in the above table;

Q12. Amit creates a database name contacts but he is not able to create the table. What
command should be used before creating the table?
Q13. A table Student has 4 rows and 2 Column and another table has 3 rows and 4 columns.
How many rows and columns will be there if we obtain the Cartesian product of these
two tables?
Q14. Mr. Sanghi created two tables with City as Primary Key in Table1 and Foreign key in
Table2 while inserting row in Table2 Mr Sanghi is not able to enter value in the column
City. What is the possible reason for it?
Q15. What is difference between curdate() and date() functions?
Q16. There is column salary in table employee. The following two statements are giving
different outputs. What may be the possible reasons?
Select count(*) from employee select count(salary) from employee
Q17. Give One difference between Rollback and Commit?
Q18. What is View?
Q19. TABLE ‐GRADUATE
S.NO NAME STIPEND SUBJECT AVERAGE DIV.
1 KARAN 400 PHYSICS 68 I
2 DIWAKAR 450 COMP. Sc. 68 I
3 DIVYA 300 CHEMISTRY 62 I
4 REKHA 350 PHYSICS 63 I
5 ARJUN 500 MATHS 70 I
6 SABINA 400 CEHMISTRY 55 II
7 JOHN 250 PHYSICS 64 I
8 ROBERT 450 MATHS 68 I
9 RUBINA 500 COMP. Sc. 62 I
10 VIKAS 400 MATHS 57 II
(a) List the names of those students who have obtained DIV I sorted by NAME.
(b) Display a report, listing NAME, STIPEND, SUBJECT and amount of stipend received in a
year assuming that the STIPEND is paid every month.
(c.) To count the number of students who are either PHYSICS or COMPUTER SC graduates.
(d) To insert a new row in the GRADUATE table 11,”KAJOL”, 300, “COMP. SC.”, 75, 1
(e) Give the output of following sql statement based on table GRADUATE:
i. Select MIN(AVERAGE) from GRADUATE where SUBJECT=”PHYSICS”;
ii. Select SUM(STIPEND) from GRADUATE WHERE div=2;
iii. Select AVG(STIPEND) from GRADUATE where AVERAGE>=65;
iv. Select COUNT(distinct SUBDJECT) from GRADUATE;
Assume that there is one more table GUIDE in the database as shown below:
Table ‐ GUIDE
MAINAREA ADVISOR
PHYSICS VINOD
COMPUTER SC ALOK
CHEMISTRY RAJAN
MATHEMATICS MAHESH
(f) What will be the output of the following query:
SELECT NAME, ADVISOR FROM GRADUATE,GUIDE WHERE SUBJECT= MAINAREA;

Q20. Write the SQL query commands based on following table

Table : Book
Book_id Book name Author_name Publisher Price Type Quantity
C0001 Fast Cook Lata Kapoor EPB 355 Cookery 5
First
F0001 The Tears William Hopkins Publi. 650 Fiction 20
T0001 My First c++ Brain & Brooke FPB 350 Text 10
T0002 C++ Brain works A.W. Rossaine TDH 350 Text 15
F0002 Thunderbolts Anna Roberts First Publ. 750 Fiction 50

Table : issued
Book_Id Quantity Issued
T0001 4
C0001 5
F0001 2

Write SQL query for (a) to (f)

a. To show book name, Author name and price of books of First Pub. Publisher
b. To list the names from books of text type
c. To Display the names and price from books in ascending order of their prices.
d. To increase the price of all books of EPB publishers by 50.
e. To display the Book_Id, Book_name and quantity issued for all books which have been
issued
f. To insert a new row in the table issued having the following data. ‘F0003’, 1
g. Give the output of the following
I. Select Count(*) from Books
II. Select Max(Price) from books where quantity >=15
III. Select book_name, author_name from books where publishers=’first publ.’
IV. Select count(distinct publishers) from books where Price>=400
Q21. Write the SQL commands for the (i) to (iv) and write the output of the (v) to (viii) on the
basis of table CLUB.
Table: CLUB
COACH_ID COACHNAME AGE SPORTS DATOFAPP PAY SEX
1 KUKREJA 35 KARATE 27/03/1996 10000 M
2 RAVINA 34 KARATE 20/01/1997 12000 F
3 KARAN 34 SQUASH 19/02/1998 20000 M
4 TARUN 33 BASKETBALL 01/01/1998 15000 M
5 ZUBIN 36 SWIMMING 12/01/1998 7500 M
6 KETAKI 36 SWIMMING 24/02/1998 8000 F
7 ANKITA 39 SQUASH 20/02/1998 22000 F
8 ZAREEN 37 KARATE 22/02/1998 11000 F
9 KUSH 41 SWIMMING 13/01/1998 9000 M
10 SHAILYA 37 BASKETBALL 19/02/1998 17000 M
a. To show all information about the swimming coaches in the club.
b. To list names of all coaches with their date of appointment (DATOFAPP) in descending
order.
c. To display a report, showing coachname, pay, age and bonus (15% of pay) for all the
coaches.
d. To count the number of coaches in each sports.
e. Give the output of following SQL statements:
I. SELECT COUNT( DISTINCT SPORTS) FROM CLUB;
II. SELECT SUM(PAY) FROM CLUB WHERE DATOFAPP>‘31/01/1998’;
III. SELECT LCASE(SPORTS) FROM CLUB;
IV. SELECT MOD(AGE,5) FROM CLUB WHERE SEX= ‘F’;

Q22. Write SQL queries for (i) to (iv) and find outputs for SQL queries (v) to (viii), which are
based on the tables.
Table: ACCOUNT
ANO ANAME ADDRESS
101 Nirja Singh Bangalore
102 Rohan Gupta Chennai
103 Ali Reza Hyderabad
104 Rishabh Jain Chennai
105 Simran Kaur Chandigarh

Table: TRANSACT
TRNO ANO AMOUNT TYPE DOT
T001 101 2500 Withdraw 2017‐12‐21
T002 103 3000 Deposit 2017‐06‐01
T003 102 2000 Withdraw 2017‐05‐12
T004 103 1000 Deposit 2017‐10‐22
T005 101 12000 Deposit 2017‐11‐06

(i) To display details of all transactions of TYPE Deposit from Table TRANSACT
(ii) To display the ANO and AMOUNT of all Deposits and Withdrawals done in the month of
October 2017 from table TRANSACT.
(iii) To display the last date of transaction (DOT) from the table TRANSACT for the Accounts
having ANO as 103.
 (iv) To display all ANO, ANAME and DOT of those persons from tables ACCOUNT and
TRANSACT who have done transactions less than or equal to 3000.
(v) SELECT ANO, ANAME FROM ACCOUNT WHERE ADDRESS NOT IN ('CHENNAI',
'BANGALORE');
(vi) SELECT DISTINCT ANO FROM TRANSACT;
(vii) SELECT ANO, COUNT(*), MIN(AMOUNT) FROM TRANSACT GROUP BY ANO HAVING
COUNT(*)> 1;
(viii) SELECT COUNT(*), SUM(AMOUNT) FROM TRANSACT WHERE DOT <= '2017‐06‐01';
(ix) Identify the foreign key from the above given tables.

Q23. What are indexes

 SOLUTIONS ‐ MYSQL
Ans 1. A database management system (DBMS) is system software for creating and
managing databases. The DBMS provides users and programmers with a systematic way
to create, retrieve, update and manage data
Ans 2. A data model refers to a set of concepts to describe the structure of a database, and
certain constraints (restrictions) that the database should obey. The four data model
that are used for database management are :
1. Relational data model : In this data model, the data is organized into tables (i.e. rows
and columns). These tables are called relations.
2. Hierarchical data model
3. Network data model
4. Object Oriented data model

Ans 3. Data redundancy means duplication of data. It causes duplicate data at different
locations which destroys the integrity of the database and wastage of storage space.

Ans 4.
Internal Level Conceptual Level External Level
Describe show the data is Describes what data are Concerned with the data is
actually stored on the storage actually stored in the database viewed by individual users.
medium. and relationship existing
among data.
At this level, complex low‐ At this level, the database is Only a part of the database
level data structure are described logically in terms of relevant to the users is
described in details. simple data‐structures. provided to them through this
level.

Ans 5. Data independence is the ability to modify a scheme definition in one level without
affecting a scheme definition in a higher level. Data independence types are
1. Physical Data Independence has ability to modify the scheme followed at the physical
level without affecting the scheme followed at the conceptual level.
2.Logical Data Independence has ability to modify the conceptual scheme without
causing any changes in the schemes followed at view levels.

Ans 6. SQL is a language that enables you to create and operate on relational databases, which
are sets of related information stored in tables.
Ans 7
1. Relation : A table storing logically related data is called a Relation.
2. Tuple : A row of a relation is generally referred to as a tuple.
3. Attribute : A column of a relation is generally referred to as an attribute.
4. Degree : This refers to the number of attributes in a relation.
5. Cardinality : This refers to the number of tuples in a relation.
6. Primary Key : This refers to a set of one or more attributes that can uniquely identify tuples
within the relation.
7. Candidate Key : All attribute combinations inside a relation that can serve as primary key are
candidate keys as these are candidates for primary key position.
8. Alternate Key : A candidate key that is not primary key, is called an alternate key.
9. Foreign Key : A non‐key attribute, whose values are derived from the primary key of some
other table, is known as foreign key in its current table.
Ans 8. Integrity Constraints Integrity constraints are a set of rules. It is used to maintain the
quality of information. Integrity constraints ensure that the data insertion, updating, and
other processes have to be performed in such a way that data integrity is not affected.
Thus, integrity constraint is used to guard against accidental damage to the database.
Types of Integrity Constraint
1. Domain constraints :
Domain constraints can be defined as the definition of a valid set of values for an attribute.
The data type of domain includes string, character, integer, time, date, currency, etc.
The value of the attribute must be available in the corresponding domain.
Example:

2. Entity integrity constraints

The entity integrity constraint states that primary key value can't be null.
This is because the primary key value is used to identify individual rows in relation and if the
primary key has a null value, then we can't identify those rows.
A table can contain a null value other than the primary key field.
Example:

3. Referential Integrity Constraints

Example:

4. Key constraints
Keys are the entity set that is used to identify an entity within its entity set uniquely.
An entity set can have multiple keys, but out of which one key will be the primary key.
A primary key can contain a unique and null value in the relational table.
Example:
Ans 9. Classification of Sql Statements
a. DDL(Data Definition Language): It is set of SQL commands used to create, modify
and delete database structures but not data. Commands in DDL are
1. Create – to create the objects in database
2. Alter – alters the structures of the database
3. Drop – Delete objects from the database
b. DML(Data Manipulation Language): It is the area of SQL that allows changing the
dat within the database. Command in DML are
1. insert : insert data into the table
2. Update Updates Existing data within the table
3. Delete : Delete the records from the table
c. TCL(Transaction Control Language): Commands theat allow to manage and control
the transactions Commands are
1. Commit : making changes to database permanent
2. Rollback : undoing changes to database permanent
3.SavePoint : Identiy a point in a transaction to which we can later roll back
4 SET TRANSACTION :Change transaction option like what roll back segment to use

Ans 10 .
Create table dept(deptno integer Primary Key,dname integer(20), loc varchar(10));

Create table emp(empno integer Primary Key, Ename varchar(20) NOT NULL, job Char(10),
mgr integer, hiredate date, sal decimal(9,2) check(sal>0),comm integer, deptno integer
references dept(deptno) on delete cascade);

Ans 11.
a) select * from emp where deptno=20 or job=’salesman’;
b) select empno,ename from emp where job=’Manger’;
c) select * from emp where deptno=20 and job=’clerk’;
d) select * from emp where hiredate<’2014‐09‐01’;
e) select * from emp where job!=’manager’;
f) select * from emp where empno in(7369,7521,7839,7934,7788);
g) select empno, ename from emp where empno between 1000 and 2000;
h) select ename from emp where hiredate not between ‘2014‐06‐30’ and ’2014‐12‐31’;
i) select distinct(job) from emp;
j) select * from emp where comm is NULL;
k) select ename from emp where ename like ‘S%’;
l) select ename from emp where ename like’ _____’;
m) select ename from emp where ename like ‘_I%’;
n) select empno,ename,sal from emp order by sal;
o) select empno,ename from emp order by hiredate desc;
p) select ename, sal,sal*,5 as “hra”,sal*.1 as “pf”, sal*.3 as “da”, sal+sal*.5+sal*.3‐sal*.1 as “gross” order
by sal+sal*.5+sal*.3‐sal*.1;
q) select count(*) from emp ;
r) select count(distinct job) from emp;
s) select depnto,sum(sal) from emp group by deptno;
t) select job, count(*) from emp group by job order by count(*) desc;
u) select sum(sal),max(sal),min(sal),avg(sal) from where deptno=20 emp group by job;
v) select depnto,job,deptno from emp group by deptno,job;
w) select avg(sal) from emp group by deptno having count(*)>5;
x) select sum(sal),max(sal),min(sal),avg(sal) from emp where deptno=20 group by job having
avg(sal)>1000 order by sum(sal);
y) select empno,ename, e.deptno,dname from emp e, dept d where e.deptno=d.deptno;
z) select empno,ename, sal, sal+ifnull(comm,0) as “total salary”from emp;
aa) alter table emp add column address varchar(20);
bb) alter table emp add constraing pk_1 Primay key(empno);
cc) alter table emp add constraint fk_1 Foreign Key deptno references (dept(deptno) on delete cascade)
dd) alter table emp Modify sal decimal(15,2);
ee) alter table emp drop column address;
Ans 12. Use Contacts
Ans 13 12 rows and 6 columns
Ans 14 Mr Sanghi was trying to enter the name of City in Table2 which is not present in
Table1 i.e. Referential Integrity ensures that value must exist in referred table.
Ans 15 curdate() returns the current date whereas date() extracts the date part of a date.
Ans 16. The possible reason is that the salary filed may contain null values so count(salary)
will not count that record.
Ans 17. Rollback command is used to end the current transaction and Undo all the changes
we made since current transaction begin While Commit is used to make all changes
permanent to underlying database which we made during the current transaction.
Ans 18. View is a virtual table that does not e xists physically. Data in view is derived from
original table .
create view v1 as select empno,ename from emp where deptno=10;
Ans 19. (a) SELECT NAME FROM GRADUATE WHERE DIV='I' ORDER BY NAME;
(b) SELECT NAME, STIPEND, SUBJECT, STIPEND*12 STIPEND_YEAR FROM GRADUATE;
(c) SELECT SUBJECT, COUNT(NAME) FROM GRADUATE GROUPBY (SUBJECT) HAVING
SUBJECT='PHYSICS' OR SUBJECT='COMP. Sc.';
(d) INSERT INTO GRADUATE VALUES(11,'KAJOL',300,'COMP. Sc.',75,1);
(e) (i) MIN(AVERAGE) 63
(ii) SUM(STIPEND) 800
(iii) AVG(STIPEND) 420
(iv) COUNT(DISTINCT SUBJECT) 4
(f) SELECT NAME, ADVISOR FROM GRADUATE, GUIDE WHERE SUBJECT = MAINAREA;
NAME ADVISOR
DIVYA RAJAN
SABINA RAJAN
KARAN VINOD
REKHA VINOD
JOHN VINOD
Ans 20
a) Select book_name, author_name , price from books where publisher=’First Publ’
b) Select book_name from books where type=’Text’
c) Select book_name, price from books Order by Price;
d) Update books set price=price+50 where publishers=’EPB’
e) Select a.book_id,a.book_name,b.quantity_issued from books a, issued b where
a.book_id=b.book_id
f) Insert into issued Values (‘F0003’,1);
g) (i) 5 (ii) 750 (iii)Fast Cook Lata Kappor
(iv)My First c++ Brain & Brooke
Ans 21. i. SELECT * FROM CLUB WHERE SPORTS=’SWIMMING’;
ii. SELECT COACHNAME,DATOFAPP FROM CLUB ORDER BY DATOFAPP DESC;
iii. SELECT COACHNAME, PAY, AGE, PAY *0.15 AS BONUS FROM CLUB ;
iv. SELECT COUNT(COACHNAME) FROM CLUB GROUP BY SPORTS
v. (a) 4
(b). 78000
(c)
Karate
Karate
Squash
Basketball
Swimming
Swimming
Squash
Karate
Swimming
Basketball
(d) 4 6 9 7

Ans 22
(i) SELECT * FROM TRANSACT WHERE TYPE = 'Deposit';
(ii) SELECT ANO,AMOUNT FROM TRANSACT WHERE DOT >= '2017‐10‐01' AND DOT <= '2017‐10‐31';
OR
SELECT ANO,AMOUNT FROM TRANSACT WHERE DOT BETWEEN '2017‐10‐01' AND '2017‐10‐31';
(iii) SELECT MAX(DOT) FROM TRANSACT WHERE ANO = 103;
(iv) SELECT A.ANO, ANAME, DOT FROM ACCOUNT A, TRANSACT T WHERE A.ANO = T.ANO AND
AMOUNT <=3000;
OR
SELECT A.ANO,ANAME,DOT FROM ACCOUNT A,TRANSACT T WHERE A.ANO=T.ANO AND AMOUNT
<=3000;

(v) ANOANAME
103 Ali Reza
105 Simran Kaur

(vi) DISTINCT ANO

101
102
103

(vii) ANOCOUNT(*)MIN(AMOUNT)
101 2 2500
103 2 1000

(viii) COUNT(*)SUM(AMOUNT)
2 5000

(ix) Ano in Transact table

Ans 23. An index is a data structure maintained by database that helps it find records within
a table more quickly. Eg. To create index : create index id on emp(deptno);