Mysql - WS 1 - 10

MYSQL - WORKSHEET
My SQL Worksheet-1
(DDL – Database Related commands)
1. If a database "Employee" exists, which MySql command helps you to start working
in that database?
2. Write MySql command will be used to open an already existing database

"LIBRARY".
3. Write MySql command to open an existing database.
4. What does SQL stand for? What is MySQL?
5. Write two examples of DBMS software.
6. Sharmila wants to make the database named ‘COMPANY’ active. Write MySQL
commands for it.
7. What is MySQL ?
8. What is the relationship between SQL and MySQL ?
9. Mention any two example of common Database Management System.
10. Suggest Archana suitable command for the following purpose:

i. To display the list of the database already existing in MySQL.
ii. To use the database named City.
iii. To remove the pre-existing database named Clients.
i.
ii.
iii.
11. Write the command to display the name of the active database.
12. Write the command to create a new database “School”

My SQL Worksheet-2
(DDL – Table Related commands excluding Alter table)
1. Write an SQL query to create the table 'Menu' with the following structure:
2. Can a table have multiple primary keys? Can it have multiple foreign keys?
3. In a Student table, out of Roll Number, Name, Address which column can be set as Primary
key and why?
4. Ms. Mirana wants to remove the entire content of a table "BACKUP" alongwith its
structure to release the storage space. What MySql statement should she use ?
5. Write MySql command to create the Table STOCK including its Constraints.
Table STOCK :
6. Write one similarity and one difference between CHAR and VARCHAR data types.
7. Saumya had previously created a table named ‘Product’ in a database using MySQL.
Later on she forgot the table structure. Suggest her suitable MySQL command through
which she can check the structure of the already created table.
8. Roli wants to list the names of all the tables in her database named ‘Gadgets’. Which
command (s) she should use to get the desired result.
9. Name the SQL commands used to :
(i) Physically delete a table from the database.
(ii) Display the structure of a table.
10. Write one similarity and one difference between UNIQUE and PRIMARY KEY constraints.
11. An attribute A of datatype varchar(20) has the value “Amit” . The attribute B of datatype
char(20) has value ”Karanita” . How many characters are occupied in attribute A ? How
many characters are occupied in attribute B?
12. Mrs. Sharma is the classteacher of Class ‘XII A’ She wants to create a table ‘Student’
to store details of her class.
i) Which of the following can be the attributes of Student table?
a) RollNo b) “Amit” c) Name d) 25
ii) Name the Primary key of the table ‘Student’. State reason for choosing it.
13. Write SQL query to create a table ‘Player’ with the following structure:
14. Anita has created the following table with the name ‘Order’.
One of the rows inserted is as follows :
(i) What is the data type of columns OrderId and OrderDate in the table Order ?
(ii) Anita is now trying to insert the following row :
Will she be able to successfully insert it ? Give reason.
15. Write SQL query to create a table ‘Event’ with the following structure :
Field Type Constraint
EventId Varchar(5) PRIMARY KEY
EventName Varchar(30) NOT NULL
Location Varchar(50)
ClientID Integer
EventDate Date
16. Observe the given table carefully and answer the following questions:
i. Name the column that might have a Primary Key constraint. Justify your answer.
ii. Name the column that might have a Unique constraint. Justify your answer.
17. “ABC” Event Management Company requires data of events that are to be organized.
Write SQL query to create a table ‘Event’ with the following structure :
18. suggest her suitable command for the following purpose:

iv. To display the list of the database already existing in MySQL.
v. To use the database named City.
vi. To remove the pre-existing database named Clients.
vii. To remove all the records of the table named “Club” at one go along with its
structure permanently.
19. While creating a table named “Employee”, Mr. Rishi got confused as which data type he
should chose for the column “EName” out of char and varchar. Help him in choosing
the right data type to store employee name. Give valid justification for the same.
My SQL Worksheet-3
(DDL – Alter Table commands)
1. Sahil created a table in Mysql. Later on he found that there should have been another
column in the table. Which command should he use to add another column to the table?
2. While creating a table 'Customer' Simrita forgot to set the primary key for the table. Give
the statement which she should write now to set the column 'CustiD' as the primary key
of the table?
3. Kuhu has already created a table ‘Hospital’ as shown below:
Now she wants to add a new column ‘Address’ to the above given table. Suggest suitable
MySQL command for the same.
4. Write SQL command to remove column named ‘Hobbies’ from a table named ‘Student’.
5. While creating the table Student last week, Ms. Sharma forgot to include the column
Game_Played. Now write a command to insert the Game_Played column with VARCHAR
data type and 30 size into the Student table?
6. Kunal created the following table with the name ‘Friends’ :

Table :
Friends
FriendCode Name Hobbies
F101 Bijoy Swimming
F102 Abhinav Reading books
F103 Jyotsna Dancing
Now, Kunal wants to delete the ‘Hobbies’ column. Write the MySQL statement
7. Rashi wants to add another column ‘Hobbies’ with datatype and size as VARCHAR(50) in
the already existing table ‘Student’. She has written the following statement. However it
has errors. Rewrite the correct statement.
MODIFY TABLE Student Hobbies VARCHAR;
8. Ms. Shalini has just created a table named “Employee” containing columns
Ename, Department, Salary.
After creating the table, she realized that she has forgotten to add a primary key
column in the table. Help her in writing SQL command to add a primary key column
empid. Also state the importance of Primary key in a table.
9. While creating a table 'Customer' Simrita wrongly added a primary key constraint to
the field “CUSTNAME”. Now she wants to remove the primary key constraint from the
custname field. Help her in writing the correct command.
10. Mr. Akshat have added a not null constraint to the “name” field in “employees” table.
But now he wants to remove that not null constraint. Write the command to delete the
not null constraint from name field.
My SQL Worksheet-4
(DML – INSERT INTO commands)
1. Rama is not able to change a value in a column to NULL. What constraint did she specify when she
created the table?
2. Consider the table RESULT given below.
Write command to insert a new row

6, "Mohan", 500, "English", 73, "Second"
3. Consider the Table SHOPPE given below.
To insert a new row in the table Shoppe

'110', 'Pizza' , 'Papa Jones', 120, "Kolkata", 50.0
4. How is NULL value different from 0 (Zero) value?
5. Consider the following table named "GYM"
Add a new row for a new item in GYM with the details: "G107", "Vibro exerciser” ,21000
“GTCFitness"
6. What is meant by NULL value in MySQL?
7. Rewrite the following SQL statement after correcting error(s). Underline the corrections made.
INSERT IN STUDENT(RNO,MARKS) VALUE (5,78.5);
8. Rewrite the following SQL statement after correcting error(s). Underline the corrections made.
INSERT IN EMP(EMPNO, SALES) VALUE (100, 20078.50);
9. Charvi is inserting “Sharma” in the “LastName” column of the “Emp” table but an error is bein
displayed. Write the correct SQL statement.
INSERT INTO Emp(‘Sharma’)VALUES(LastName) ;
10. Anita has created the following table with the name ‘Order’.
One of the rows inserted is as follows :
(i) What is the data type of columns OrderId and OrderDate in the table Order ?
(ii) Anita is now trying to insert the following row :
Will she be able to successfully insert it ? Give reason.
11. In today’s digitized world with a need to store data electronically, it is very important to store the
data in the databases. SQL is used to interact with the Database Management System.
Classify the following commands according to their type :(DDL/DML)
i. INSERT INTO ii. ALTER TABLE
12. Is NULL and 0(zero) same? Jusify your answer.
13. Write the full forms of the following:

i. DDL ii. DML
My SQL Worksheet-5
(DML – UPDATE and DELETE commands)
1. What is the purpose of DROP TABLE command in SOL? How is it different from
DELETE command?
2. In a database there are two tables "Product" as shown below :
Write the command To increase the Price of all the Products by 20.
3. Write the UPDATE command to change “Sharma” to “Singh” in the “LastName” column
in the Employee table.
4. What is the use of UPDATE statement in SQL ? How is it different from ALTER
statement?
Write command To change the Brandname to "Fit Trend India" of the item, whose
ICODE as "G101 ".
6. Write the UPDATE statement in MySQL to increase commission by 100.00 in the

‘‘Commission’’ column in the ‘Emp’ table.
7. Write two examples of DML commands of SQL.
8. In a database there are two tables ‘CD’ and ‘TYPE’ as shown below :
Write SQL statement to change the name of Singer ‘‘Sonvi Kumar’’ to ‘‘Sonvi Mehra’’
in all the places wherever it occurs in CD table.
9. Consider the following table named “GARMENT”.
1) Write command To change the colour of garment with code as 116 to “Orange”.
2) Write command to increase the price of all XL garments by 10%
3) Write command to delete the record with GCode “116”
10 In a Database, there are two tables given below :

.
Write SQL command to change the JOBID to 104 of the Employee with ID as E4 in the
table ‘EMPLOYEE’.
11 In Marks column of ‘Student’ table, for Rollnumber 2, the Class Teacher entered the
. marks as 45. However there was a totaling error and the student has got her marks
increased by 5. Which MySQL command should she use to change the marks in
‘Student’ table.
12 Chhavi has created a table named Orders, she has been asked to increase the value of
. a column named salesamount by 20. She has written the following query for the same.
Alter table Orders Add salesamount =salesamount+20;
Is it the correct query?Justify.
13 Consider the following table:

. Table: PharmaDB
Write commands in SQL to increase the price of “Amlodipine” by 50.

My SQL Worksheet-6
(DML – SELECT command)
1. Pooja, a students of class XI, created a table "Book". Price is a column of this table. To find
the details of books whose prices have not been entered she wrote the following query:
Select * from Book where Price = NULL;
2. The LastName column of a table "Directory" is given below:

Based on this information, find the output of the following
queries:
a) SELECT lastname FROM Directory WHERE lastname like
"_a%";
b)SELECT lastname FROM Directory WHERE lastname not like

"%a";
3. Consider the table TEACHER given below. Write commands in SQL for (1) to (3) and output
for (4)
i. To display all information about teachers of PGT category.

ii. To list the names of female teachers of Hindi department.
iii. To list names, departments and date of hiring of all the teachers in ascending order of date
of joining
iv. SELECT DISTINCT(category) FROM teacher;
4. The ltem_No and Cost columna of a table "ITEMS" are given below:
Based on this information, find the output of the following
queries:
a) SELECT COST +100 FROM ITEMS WHERE ITEM_NO >
103;
5. Consider the table Projects given below. Write commands in SOL for i) to iii) and output for
iv)
i. To display all information about projects of"Medium" ProjSize
ii. To list the ProjSize of projects whose ProjName ends with LITL.
iii. To list ID, Name, Size, and Cost of all the projects in descending order of StartDate.
iv. SELECT DISTINCT ProjSize FROM projects
6. The Mname Column of a table Members is given below :

Based on the information, find the output of the following
queries :
(i) Select Mname from members where mname like "%v" ;
(ii) Select Mname from members where mname like "%e%";
7. Sarthya, a student of class XI, created a table "RESULT". Grade is one of the column of this
table. To find the details of students whose Grades have not been entered, he wrote the
following MySql query, which did not give the desired result.
SELECT * FROM Result WHERE Grade= "Null";
Help Sarthya to run the query by removing the errors from the query and write the correct
Query.
8. Consider the table RESULT given below. Write commands in MySql for (i) to (ii)
(i) To list the names of those students,

who have obtained Division as FIRST
in the ascending order of NAME.
(ii) To display a report listing NAME,
SUBJECT and Annual stipend received
assuming that the stipend column has
monthly stipend.
9. Mr. Janak is using a table with following columns :

Name , Class , Course_Id, Course_name
He needs to display names of students, who have not been assigned any stream or have been
assigned Course_name that ends with "economics". He wrote the following command, which
did not give the desired result.
SELECT Name, Class FROM Students WHERE Course name = NULL OR Course
name="%economics";
Help Mr. J anak to run the query by removing the error and write the correct query.
10. Consider the Table SHOPPE given below. Write command in MySql for (i) to (ii)
(i) To display names of the items whose

name starts with 'C' in ascending order of
Price.
(ii) To display Code, Item name and City of
the products whose quantity is less than 100.
11. What is used in the SELECT clause to return all the columns in the table?
12. In MySQL, Sumit and Fauzia are getting the following outputs of ItemCodes for SELECT
statements used by them on a table named ITEM.(Both have used the SELECT statements on
the same table ITEM).
Sumit’s Output Fauzia’s Output
101 101
102 102
101 105
105 107
101
107
Which extra keyword has Fauzia used with SELECT statement to get the above output?
13. Consider the table ‘PERSONS’ given below. Write commands in SQL for (i) to (iv) and write
output for (v).
(i) Display the SurNames, FirstNames

and Cities of people residing in Udhamwara
city.
(ii) Display the Person Ids (PID), cities
and Pincodes of persons in descending order
of Pincodes.
(iii) Display the First Names and cities of
all the females getting Basic salaries above
40000.
(iv) Display First Names and Basic
Salaries of all the persons whose firstnames starts with “G”.
(v) SELECT Surname FROM Persons Where BasicSalary>=50000;
14. Mr. Tondon is using table EMP with the following columns.
ECODE,DEPT,ENAME,SALARY
He wants to display all information of employees (from EMP table) in ascending order of
ENAME and within it in ascending order of DEPT. He wrote the following command, which
did not show the desired output.
SELECT * FROM EMP ORDER BY NAME DESC,DEPT;
Rewrite the above query to get the desired output.
15. Consider the following table named "GYM" with details about fitness items being sold in the
store. Write command of SQL for (i) to (ii).
(i) To display the names of all the items whose name

starts with "A".
(ii) To display ICODEs and INAMEs of all items,
whose Brandname is Reliable or Coscore.
16. Consider the following table named 'SBOP" with details of account holders. Write commands
of MySql for (i) to (ii) and output for (iii).
(i) To display Accountno, Name and DateOfopen of

account holders having transactions more than 8.
(ii) To display all information of account holders whose
transaction value is not mentioned.
(iii) SELECT NAME,BALANCE FROM SBOP WHERE
NAME LIKE “%i”;
17. When using the LIKE clause, which wildcard symbol represents any sequence of none, one or
more characters ?
18. Consider the table FLIGHT given below. Write commands in SQL for (i) to (iv) and output for
(v).
(i) Display details of all flights starting from Delhi.
(ii) Display details of flights that have more than 4 number of flights operating.
(iii) Display flight codes, starting place, destination, number of flights in descending order of
number of flights.
(iv) Display destinations along with flight codes of all the destinations starting with ‘A’.
(v) SELECT DISTINCT(NO_STOPS) FROM FLIGHT;
19. What will be the output of the following queries on the basis of Employee table:
(i) Select Salary+100 from Employee where EmpId='A002';
20. Pranay, who is an Indian, created a table named “Friends” to store his friend’s detail.
Table “Friends” is shown
below. Write commands
in SQL for (i) to (iii) and
output for (iv).
i. To display list of all foreigner friends.

ii. To list name, city and country in descending order of age.
iii. To list name and city of those friends who don’t have an email id.
iv. Select name,country from friends where age>12 and name like ‘A%’;
21. Consider the following table named “GARMENT”. Write command of SQL for (i)
to (iv) and output for (v) to (vii).
(i) To display names of those garments
that are available in ‘XL’ size.
(ii) To display codes and names of those
garments that have their names starting
with ‘Ladies’.
(iii) To display garment names, codes and
prices of those garments that have
price in the range 1000.00 to 1500.00
(both 1000.00 and 1500.00 included).
(iv) SELECT GNAME FROM GARMENT WHERE SIZE IN (‘M’, ‘L’) AND PRICE > 1500;
22. Consider the table ‘empsalary’.
To select tuples with some salary ,Siddharth has written the following
erroneous SQL
statement:
SELECT ID, Salary FROM empsalary WHERE Salary = something;
23. Consider the table ‘Employee’.

Write the SQL command to obtain the following output :
24. Table “Emp” is shown below. Write commands in SQL for (i) to (iii) and output for (iv) and
(v)
and (vi)
i. To display list of all employees below 25
years old.
ii. To list names and respective salaries in
descending order of salary.
iii. To list names and addresses of those
persons who have ‘Delhi’ in their address.
iv. SELECT Name, Salary FROM Emp where
salary between 50000 and 70000;
v. SELECT Name, phone from emp where phone like ‘99%’;
25. Mrs. Sen entered the following SQL statement to display all Salespersons of the cities
“Chennai” and ‘Mumbai’ from the table ‘Sales’.
Scode Name City SELECT * FROM Sales WHERE
101 Aakriti Mumbai City=‘Chennai’ AND
City=‘Mumbai’;
102 Aman Chennai
103 Banit Delhi
104 Fauzia Mumbai
Rewrite the correct statement, if wrong or write statement is correct.
26. Write commands in SQL for (i) to (iii) and output for (iv).
Table :
Store
StoreId Name Location City NoOfEmployees DateOpened SalesAmount
S101 Planetfashion KarolBagh Delhi 7 2015-10-16 300000
S102 Trends Nehru Mumbai 11 2015-08-09 400000
Nagar
S103 Vogue Vikas Delhi 10 2015-06-27 200000
Vihar
S104 Superfashion Defence Delhi 8 2015-02-18 450000
Colony
S105 Rage Bandra Mumbai 5 2015-09-22 600000
(i) To display name, location, city, SalesAmount of stores in descending order of
SalesAmount.
(ii) To display names of stores along with SalesAmount of those stores that have ‘fashion’
anywhere in their store names.
(iii) To display Stores names, Location and Date Opened of stores that were opened before
1st March, 2015.
(iv) SELECT distinct city FROM store;
27. Which clause would you use with Select to achieve the following:
i.To select the values that match with any value in a list of specified values.
ii.Used to display unrepeated values of a column from a table.
28. Consider the following table:

Table: PharmaDB
Write commands in SQL for (i) to (iii)
and output for (iv):
i. To increase the price of “Amlodipine”
by 50.
ii. To display all those medicines whose
price is in the range 100 to 150.
iii. To display the Drug ID, DrugName
and Pharmacy Name of all the records in
descending order of their price.
iv. SELECT RxID, DrugName, Price from PharmaDB where PharmacyName IN (“Rx
Parmacy”, “Raj Medicos”);
29. Write SQL statement that gives the same output as the following SQL statement but uses ‘IN’
keyword.
SELECT NAME FROM STUDENT WHERE STATE = ‘VA’ ;
30. Which one of the following SQL queries will display all Employee records containing the word
“Amit”, regardless of case (whether it was stored as AMIT, Amit, or amit etc.) ?
(i) SELECT * from Employees WHERE EmpName like UPPER ‘%AMIT%’;
(ii) SELECT *from Employees WHERE EmpName like ‘%AMIT%’ or ‘%AMIT%’ OR ‘%amit%’;
(iii) SELECT * from Employees WHERE UPPER (EmpName) like ‘%AMIT%’;
31. Write Answer to (i). Write SQL queries for (ii) to (vii).
Note : Columns SID and DOB contain Sales Person Id and Data of Birth respectively.
(i) Write the data types of SID and DOB columns.
(ii) Display names of Salespersons and their Salaries who have salaries in the range 30000.00
to 40000.00
(iii) To list Names, Phone numbers and DOB (Date of Birth) of Salespersons who were born
before 1st November, 1992.
(iv) To display Names and Salaries of Salespersons in descending order of salary.
(v) To display areas in which Salespersons are working. Duplicate Areas should not be
displayed.
(vi) To display SID, Names along with Salaries increased by 500. (Increase of 500 is only to
be displayed and not to be updated in the table)
(vii) To display Names of Salespersons who have the word ‘Kumar’ anywhere in their names.
32. Write the following statement using ‘OR’ logical operator :

SELECT first_name, last_name, subject FROM studentdetailsWHERE subject IN (‘Maths’,
‘Science’);
33. Consider the Table “Gym” shown below. Write commands in SQL for (i) to (vi) :
(i) To display Mname, Age, FeeGiven of those members whose fee is above 12,000.
(ii) To display Mcode, Mname, Age of all female members of the Gym with age in descending
order.
(iii) To list names of members and their date of admission of those members who joined after
31st December, 2015.
iv) To display the Mname, FeeGiven of all those members of the Gym whose age is less than
40 and are monthly type members of the Gym.
(v) To display names of members who have ‘mit’ anywhere in their names. For example :
Amit, Samit.
(vi) To display types of memberships available. Duplicate values should not be displayed.
34. Consider the following table:
Write commands in SQL for (i) to (iv) and
output for (v):
i. To display the details of all those students
who have IP as their optional subject.
ii. To display name, stream and optional of
all those students whose name starts with
‘A’.
iii. To give an increase of 3 in the
average of all those students of humanities
section who have Maths as their
optional subject.
iv. To display a name list of all those students who have average more than 75.
v. Select name from students where optional IN (‘CS’,’IP’);
My SQL Worksheet-7
(Single Row Functions)
1. Write the output of the following SQL queries:
a) SELECT ROUND(6.5675, 2);
b) SELECT TRUNCATE(5.3456, 1);
c) SELECT DAYOFMONTH('2009-08-25');
d) SELECT MID('Class 12', 2,3);
2. Write the output of the following SQL queries :
(i) SELECT INSTR(‘UNICODE’,’CO’);
(ii) SELECT RIGHT(‘Informatics’,3);
3. State difference between date functions NOW( ) and SYSDATE( ) of MySql.
4. Name a function of MySql which is used to remove trailing and leading spaces from a string.
5. Consider the following table named 'SBOP" with details of account holders. Write output
(i) SELECT ROUND(Balance,-3) FROM SBOP

WHERE AccountNo=”SB-5”;

(i) SELECT RIGHT(‘software’, 2);
(ii) SELECT INSTR(‘twelve’,‘lv’);
(iii) SELECT DAYOFMONTH(‘2014-03-01’);
(iv) SELECT ROUND(76.987, 2);
i. SELECT INSTR(‘INTERNATIONAL’, ‘NA’);
ii. SELECT LENGTH(CONCAT(‘NETWORK’,’ING’));
iii.SELECT ROUND(563.345,-2);
iv. SELECT DAYOFYEAR(‘2014-01-30’);
8. Pranay, who is an Indian, created a table named “Friends” to store his friend’s detail.
Table “Friends” is shown below. Write output for (i) and (ii).
i. Select ucase(concat(name,”*”,city)) from friends where country like ‘Denmark’;

ii. Select mid(name,1,4) as “UID” from friends where country like ‘USA’;
i) SELECT TRUNCATE(8.975,2);
ii) SELECT MID(‘HONESTY WINS’,3,4);
iii) SELECT RIGHT(CONCAT(‘PRACTICES’,’INFORMATICS’),5);
iv) SELECT DAYOFMONTH(‘2015-01-16’);
(i) SELECT MID(‘BoardExamination’,2,4);
(ii) SELECT ROUND(67.246,2);
(iii) SELECT INSTR(‘INFORMATION FORM’,‘FOR’);
(iv) SELECT DAYOFYEAR(‘2015-01-10’);
11. Write output.
Table :
Store
StoreId Name Location City NoOfEmployees DateOpened SalesAmount
S101 Planetfashion KarolBagh Delhi 7 2015-10-16 300000
S102 Trends Nehru Mumbai 11 2015-08-09 400000
Nagar
S103 Vogue Vikas Delhi 10 2015-06-27 200000
Vihar
S104 Superfashion Defence Delhi 8 2015-02-18 450000
Colony
S105 Rage Bandra Mumbai 5 2015-09-22 600000
(i) SELECT Name, length (name), left (name, 3) FROM Store where NoOfEmployees<3;

SELECT POW(INSTR(‘My_Database’,’_’),2);
13. Consider the table given below :
Write output.
(i) SELECT Name, LENGTH(Name) FROM

Salesperson;
14. Identify Single Row functions of MySQL amongst the following :

TRIM(), MAX(), COUNT(*), ROUND()
15. Consider the Table “Gym” and write output
(i) SELECT MID(Mname,1,2)from Gym;
16. Observe the given table named “Loan”

carefully and predict the output of the
following queries:
i. SELECT concat(left(file_no,2),
right(cust_name,2)) AS “ID” from loan
where Bank='ICUCI Ltd.';
ii. select round(loan_amt-
loan_amt*10/100) As "Discounted
Payment" from loan where loan_amt>700000;
My SQL Worksheet-8
(Aggregate Functions)
1. Consider the table TEACHER given below. Write commands in SQL for (1) and output
for (2) to (5)
i. To count the number of teachers in English department.

ii. SELECT MAX(Hiredate) FROM Teacher;
iii. SELECT DISTINCT(category) FROM teacher;
iv. SELECT COUNT(*) FROM TEACHER WHERE Category = "PGT"
v. SELECT Gender,AVG(Salary) FROM TEACHER group by Gender;
2. The ltem_No and Cost column of a table "ITEMS" are given below:
Based on this information, find the output of the
following queries:
a) SELECT AVG(COST) FROM ITEMS;
b) SELECT COST +100 FROM ITEMS WHERE ITEM_NO
> 103;
3. "PrincipaiName" is a column in a table "Schools". The SOL queries

SELECT count(*) FROM Schools;
and
SELECT count( Principal) FROM schools;
Give the result 28 and 27 respectively. What may be the possible reason for this? How
many records are present in the table-27 or 28?
4. Consider the table Projects given below. Write commands in SOL fori) and output for
i) to iii)
i. To count the number of projects of cost less than 100000.

ii. SELECT SUM(Cost) FROM projects;
iii. SELECT ProjSize, COUNT(*) FROM Projects GROUP BY ProjSize;
5. Consider the table RESULT given below. Write output
(i) SELECT AVG(Stipend) FROM

EXAM WHERE DIVISION= "THIRD”;
(ii) SELECT COUNT(DISTINCT
Subject) FROM EXAM;
(iii) SELECT MIN(Average) FROM
EXAM WHERE Subject= "English";
6. What is the purpose of ORDER BY clause in MySql ? How is it different from GROUP BY
clause?
7. Consider the Table SHOPPE given below. Write command in MySql for (i) and output
for (ii) to (iii).
(i) To count distinct Company from the table.

(ii) Select Count(distinct (City)) from
Shoppe;
(iii) Select MIN (Qty) from Shoppe where
City="Mumbai";
8. Consider the table ‘PERSONS’ given below. Write commands in SQL for (i) to (iv) and
write output for (i) to (iii).
(i) SELECT SUM(BasicSalary) FROM

Persons Where Gender=’F’;
(ii) SELECT Gender,MIN(BasicSalary)
FROM Persons GROUP BY gender;
(iii) SELECT Gender,Count(*) FROM
Persons GROUP BY Gender;
9. There is a column HOBBY in a Table CONTACTS. The following two statements are
giving different outputs. What may be the possible reason ?
SELECT COUNT(*) FROM CONTACTS;
SELECT COUNT(HOBBY)FROM CONTACTS;

with details about fitness items being sold in
the store. Write output
(i) SELECT COUNT (DISTINCT (BRANDNAME)

) FROM GYM;
(ii) SELECT MAX (PRICE ) FROM GYM;
11. Consider the following table named 'SBOP" with details of account holders. Write
output.
(i) SELECT COUNT(*) FROM SBOP;
12. Given ‘Employee’ table as follows :
What values will the following

statements return ?
SELECT COUNT(*) FROM Employee;
SELECT COUNT(Commission) FROM Employee;
13. Consider the table FLIGHT given below. Write output.
(i) SELECT MAX(NO_FLIGHTS)

FROM FLIGHT;
(ii) SELECT START, COUNT(*)
FROM FLIGHT GROUP BY Start;
14. What will be the output of the following queries on the basis
of Employee table:
(i)Select avg(Salary) from Employee;

(ii) Select Salary+100 from Employee where EmpId='A002';
15. Consider the following table named

“GARMENT”. Write output
(i) SELECT COUNT(DISTINCT (SIZE)) FROM

GARMENT;
(ii) SELECT AVG (PRICE) FROM GARMENT;
16. Consider the table ‘Teacher’ given below.

What will be the output of the following
queries on the basis of the above table:
(i)Select count(Department) from

Teacher;
(ii)Select count(*) from Teacher;
17. (i) Name two Aggregate (Group) functions of SQL.
(ii) Consider the table :

Table : Company What output will be displayed by
SID SALES the following SQL
S101 20000 statement ?
SELECT AVG(SALES) FROM Company;
S103 NULL
S104 10000
S105 15000
18. Consider the table ‘Hotel’ given below :
Table : Mr. Vinay wanted to display
Hotel average salary of each Category.
EMPID Category Salary He entered the following SQL
E101 MANAGER 60000 statement. Identify error(s) and
E102 EXECUTIVE 65000 Rewrite the correct SQL statement.
E103 CLERK 40000 SELECT Category, Salary FROM
E104 MANAGER 62000 Hotel
GROUP BY Category;
E105 EXECUTIVE 50000
E106 CLERK 35000
19. Explain why the following queries give different outputs on execution:
i. SELECT COUNT(ENAME) FROM EMP;
Output: 5
ii. SELECT Count(*) FROM EMP;
Output: 8
20. Kunal has entered the following SQL command on Table ‘STUDENT’ that has
TotalMarks as one of the columns.
SELECT COUNT (*) FROM STUDENT;
The output displayed is 20.
Then, Kunal enters the following command :
SELECT COUNT (*) FROM STUDENT WHERE TotalMarks <100;
The output displayed is 15.
Then, Kunal enters the following command :
SELECT COUNT (*) FROM STUDENT WHERE TotalMarks >= 100;
He predicts the output of the above query as 5. Do you agree with Kunal ? Give reason
for your answer.
21. Consider the table given below :

Write command for (i) and output for (ii)
(i) To display Area along with number of
Salespersons working in that area.
(ii) SELECT Area, COUNT (*) FROM
Salesperson GROUP BY Area HAVING
COUNT (*) > 1;
22. Observe the given table named
“Loan” carefully and predict the
output of the
following queries:
select count(file_no)-
count(loan_amt) from loan;
My SQL Worksheet-9
(Joins)
1. In a database there are two tables 'Customer' and 'Bill' as shown below:
(i) How many rows and how many columns will be there in the Cartesian product of
these two tables?
(ii) Which column in the 'Bill' table is the foreign key?
2. Consider the tables HANDSETS and CUSTOMER given below:
With reference to these tables, Write commands in SQL for (i) and (ii) and output for (iii)
below:
(i) Display the CustNo, CustAddress and corresponding SetName for each customer.
(ii) Display the Customer Details for each customer who uses a Nokia handset.
(iii) select SetNo, SetName from Handsets, customer where SetNo = SetCode and
CustAddress = 'Delhi';
3. In a database there are two tables "Company" and "Model" as shown below:
(i) Identify the foreign key column in the table Model.

(ii) Check every value in CompiD column of both the tables. Do you find any discrepancy?
4. Consider the tables DOCTORS and PATIENTS given below:

W1th reference to these tables, wnte commands m SQL for (1) and (II) and output for
(iii) below:
(i) Display the PatNo, PatName and corresponding DocName for each patient
(ii) Display the list of all patients whose OPD_Days are MWF.
(iii) select OPD_Days, Count(*) from Doctors, Patients where Patients.Department =
Doctors.Department Group by OPD_Days;
5. In a database there are two tables "Product" and "Client" as shown below :
Write the commands in SQL queries for the following :

(i) To display the details of Product whose Price is in the range of 40 and 120 (Both
values included)
(ii) To display the ClientName, City from table Client and ProductName and Price from
table Product, with their corresponding matching P ID.
(iii) To increase the Price of all the Products by 20.
6. In a. Database School there are two tables Member and Division as show below.
(i) Identify the foreign key in the table Member.

(ii) What output, you will get, when an equi-join query is executed to get the NAME from
Member Table and corresponding DivName from Division table ?
7. In a Database there are two tables :
Table ITEM:
Write MySql queries for the following :

(i) To display ICode, IName and corresponding Brand of those Items, whose Price is
between 20000 and 45000 (both values inclusive).
(ii) To display ICode, Price and BName, of the item which has IName as "Television".
(iii) To increase the price of all the Items by 15%.
8. In a Database there are two tables :

Table MAGAZINE:
(i) Which column can be set as the PRIMARY KEY in the MAGAZINE table?
(ii) Which column in the ‘MAGAZINE’ table is the foreign key?
(iii) How many rows and columns will be there in the Cartesian product of the above 2
tables.
(iv) Write command in SQL to display the mag_code, Mag_Title and corresponding types
for all the Magazines.
(v) Write the output :
(vi) Select Mag_Code, Mag_Title, Number_of_Pages, Type From
MAGAZINE,MAGTYPE Where Magazine.Mag_Category=Magtype.Mag_Category
and Type=’Spiritual’;
9. In a Database Kamataka_Sangam there are two tables with the instances given below :
Write SQL queries for the following :
(i) To count how many addresses are not having NULL values in the address column of
students table.
(ii) To display Name, Class from STUDENT table and the corresponding Grade from
SPORTS table.
(iii) To display Name of the student and their corresponding Coachnames from
STUDENTS and SPORTS tables.
10 In a Database Multiplexes, there are two tables with the following data. Write MySQL
. queries for (i) to (iii), which are based on TicketDetails and AgentDetails :
(i) To display Tcode, Name and Aname of all the records where the number of tickets sold
is more than 5.
(ii) To display total number of tickets booked by agent “Mr. Ayush”
(iii) To display Acode, Aname and corresponding Tcode where Aname ends with “k”.
(iv) With reference to “TicketDetails” table, which column is the primary key ? Which
column is the foreign key? Give reason(s)
11 In a database there are two tables ‘CD’ and ‘TYPE’ as shown below :
.
(i) Name the Primary key in ‘‘CD’’ table.

(ii) Name the foreign key in ‘‘CD’’ table.
(iii) Write the Cardinality and Degree of ‘‘TYPE’’ table.
(iv) Check every value in CATEGORY column of both the tables. Do you find any
discrepancy ? State the discrepancy.
13 Consider the tables ‘Flights’ & ‘Fares’ given below:
. Flights
FNO SOURCE DEST NO_OF_FL NO_OF_STOP
IC301 MUMBAI BANGALORE 3 2
IC799 BANGALORE KOLKATA 8 3
MC101 DELHI VARANASI 6 0
IC302 MUMBAI KOCHI 1 4
AM812 LUCKNOW DELHI 4 0
MU499 DELHI CHENNAI 3 3
Fares
FNO AIRLINES FARE TAX
IC301 Indian Airlines 9425 5
IC799 Spice Jet 8846 10
MC101 Deccan Airlines 4210 7
IC302 Jet Airways 13894 5
AM812 Indian Airlines 4500 6
MU499 Sahara 12000 4
With reference to these tables, write commands in SQL for (i) and (ii) and output for (iii)
below:
i. To display flight number, source, airlines of those flights where fare is less than Rs.
10000.
ii. To count total no of Indian Airlines flights starting from various cities.
iii. SELECT FLIGHTS.FNO, NO_OF_FL, AIRLINES FROM FLIGHTS,FARES WHERE
FLIGHTS.FNO = FARES.FNO AND SOURCE=’DELHI’;
14 A table STUDENT has 5 rows and 3 columns. Table ACTIVITY has 4 rows and 2 columns.
. What will be the cardinality and degree of the Cartesian product of them ?
15 Consider the following table named

. “GARMENT”.
What is the degree and cardinality of ‘Garment’

table ?
16 In a Database, there are two tables given below :

.
Write SQL Queries for the following :
(i) To display employee ids, names of employees, job ids with corresponding job titles.
(ii) To display names of employees, sales and corresponding job titles who have achieved
sales more than 1300000.
(iii) To display names and corresponding job titles of those employee who have ‘SINGH’
(anywhere) in their names.
(iv) Identify foreign key in the table EMPLOYEE.
17 Consider the tables given below.

. Salesperson Orders
i. The SalespersonId column in the "Salesperson" table is the

KEY.The
SalespersonId column in the "Orders" table is a KEY.
ii. Can the ‘SalespersonId’ be set as the primary key in table ‘Orders’. Give reason.
18 With reference to the above given tables, Write commands in SQL for (i) and
. (ii) and output for (iii) below:
i. To display SalespersonID, names, orderids and order amount of all
salespersons.
ii. To display names ,salespersons ids and order ids of those sales persons whose
names start with ‘A’ and sales amount is between 15000 and 20000.
iii. SELECT Salesperson.SalespersonId, name, age, amount FROM Salesperson,
orders WHERE Salesperson.salespersonId= Orders.salespersonId AND AGE
BETWEEN 30 AND 45;
19 Consider the tables given below :

. Table :
Faculty
TeacherId Name Address State PhoneNumber
T101 Savita Sharma A-151, Adarsh Delhi 991019564
Nagar
T102 Deepak Ghai K-5/52, Vikas Mumbai 893466448
Vihar
T103 MahaLakshmi D-6 Delhi 981166568
T104 Simi Arora Mumbai 658777564
Table :
Course
CourseId Subject TeacherId Fee
C101 Introductory Mathematics T101 4500
C103 Physics T101 5000
C104 Introductory Computer Science T102 4000
C105 Advance Computer Science T104 6500
(i) Which column is used to relate the two tables ?
(ii) Is it possible to have a primary key and a foreign key both in one table ? Justify your
answer with the help of table given above.
20 With reference to the above given tables, write commands in SQL for (i) and (ii)
. and output for (iii) :
(i) To display CourseId, TeacherId, Name of Teacher, Phone Number of Teachers living in
Delhi.
(ii) To display TeacherID, Names of Teachers, Subjects of all teachers with names of
Teachers starting with ‘S’.
(iii) SELECT CourseId, Subject,Course.TeacherId,Name,PhoneNumber FROM
Faculty,Course WHERE Faculty.TeacherId = Course.TeacherId AND Fee>=5000;
21 Consider the tables given below which are linked with each other and maintains
. referential integrity:
Table: SAP
Table : Store
With reference to the above given tables, write commands in SQL for (i) and (ii) and
output for (iii) below:
i. To display the ItemCode,ItemName and ReceivedDate of all the items .
ii. To display SAPID,ItemName,ItemStorageLocation of all the items whose
Received date is after 2nd May 2016.
iii. SELECT SAPID,ItemName,STOREID FROM SAP,Store WHERE
SAP.ItemCode=Store.ItemCode AND StoreLocation = “Hauz Khas”
iv. What will be the degree and cardinality of the cartesian product formed while
combining both the above given tables ‘SAP’ and ‘Store’ ?
v. Sangeeta is not able to add a new record in the table ‘Store’ through the
following query:
Insert into store values (1206,1006,’Karol Bagh’, ‘2016/07/25’);
Identify the error if there is any
My SQL Worksheet-10
(Transaction)
1. Which command is used in MySql to make the changes in database permanent?
2. Give one difference between ROLLBACK and COMMIT commands used in MySql.
3. A table named ‘GAMES’ has the following contents:
Write the output that will be displayed by statements (i) and (ii).
SELECT * FROM GAMES;
SET AUTOCOMMIT = 0;
INSERT INTO GAMES VALUES(105,'CHESS’,2,9000);
ROLLBACK;
SAVEPOINT S1;
SELECT * FROM GAMES; ------------ (i)
INSERT INTO GAMES VALUES(108,'LAWN TENNIS’,4,25000);
SAVEPOINT S2;
INSERT INTO GAMES VALUES(109,'CRICKET’,11,20000);
ROLLBACK TO S2;
SELECT * FROM ITEM; ------------ (ii)
4. Consider the Stu table
The following SQL queries are executed on the above table
INSERT INTO Stu VALUES(5,'Gagan'); COMMIT;

UPDATE Stu SET name='Abhi' WHERE Rollno = 4
SAVEPOINT A;
INSERT INTO Stu VALUES(6,'Chris'); SAVEPOINT B;
INSERT INTO Stu VALUES(7,'Babita'); SAVEPOINT C;
ROLLBACK TO B;
What will be the output of the following SQL query now:
SELECT * FROM Stu;
5. Given below is the ‘Stu’ table :
RNO NAME
1 Amit
2 Bhishm
The following statements are entered :
SET AUTOCOMMIT = 0;
INSERT INTO Stu VALUES(5, ‘Rahul’); COMMIT;
UPDATE Stu set name=‘Rahuliya’ where Rno= 5; SAVEPOINT A;
INSERT INTO Stu VALUES(6, ‘Cristina’); SAVEPOINT B;
INSERT INTO Stu VALUES(7, ‘Fauzia’); SAVEPOINT C;
ROLLBACK TO B;
Now what will be the output of the following statement ?
SELECT * FROM Stu;
6. Geetanjali had created a table “Customer” in the database “Test”. Immediately after the
successful creation of the database, she wrote the Rollback command to undo the
creation of the table. Did she execute rollback successfully? Explain.
7. Given below is the ‘Department’ table :

SET AUTOCOMMIT = 0;
UPDATE Department SET DEPNAME = ‘OFFICE’
WHERE DEPNAME =
‘ADMIN’;
INSERT INTO Department VALUES (104, ‘HRD’);
UPDATE Department SET DEPNAME = ‘FRONT
OFFICE’ WHERE
DEPNAME = ‘RECEPTION’;
COMMIT;
DELETE FROM Department WHERE DEPNAME =
‘FRONT OFFICE’;
ROLLBACK;
SELECT * FROM Department;
What will be the output of the above given SELECT statement ?
My SQL Worksheet-11
1. Write a SQL statement that displays all the information about all
salespeople.
table: salesman
salesman_id | name | city | commission
-------------+------------+----------+------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome | 0.13
5003 | Lauson Hen | San Jose | 0.12
2. Write a SQL statement to display a string "This is SQL Exercise, Practice

and Solution".
3. Write a SQL query to display three numbers in three columns.
4. Write a SQL query to display the sum of two numbers 10 and 15 from the
RDBMS server.
5. Write an SQL query to display the result of an arithmetic expression.
6. Write a SQL statement to display specific columns such as names and

commissions for all salespeople.
Sample table: salesman
7. Write a query to display the columns in a specific order, such as order date,
salesman ID, order number, and purchase amount for all orders.
Sample table: orders
ord_no purch_amt ord_date customer_id salesman_id

---------- ---------- ---------- ----------- -----------
70001 150.5 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70002 65.26 2012-10-05 3002 5001
70004 110.5 2012-08-17 3009 5003
70007 948.5 2012-09-10 3005 5002
70005 2400.6 2012-07-27 3007 5001
70008 5760 2012-09-10 3002 5001
70010 1983.43 2012-10-10 3004 5006
70003 2480.4 2012-10-10 3009 5003
70012 250.45 2012-06-27 3008 5002
70011 75.29 2012-08-17 3003 5007
70013 3045.6 2012-04-25 3002 5001
8. From the following table, write a SQL query to identify the unique
salespeople ID. Return salesman_id.
9. From the following table, write a SQL query to locate salespeople who live
in the city of 'Paris'. Return salesperson's name, city.

salesman_id | name | city | commission
-------------+------------+----------+------------
5001 | James Hoog | New York | 0.15
5002 | Nail Knite | Paris | 0.13
5005 | Pit Alex | London | 0.11
5006 | Mc Lyon | Paris | 0.14
5007 | Paul Adam | Rome | 0.13
5003 | Lauson Hen | San Jose | 0.12
10. From the following table, write a SQL query to find customers whose
grade is 200. Return customer_id, cust_name, city, grade, salesman_id.
Sample table: customer

customer_id | cust_name | city | grade | salesman_id
-------------+----------------+------------+-------+--------
3002 | Nick Rimando | New York | 100 | 5001
3007 | Brad Davis | New York | 200 | 5001
3005 | Graham Zusi | California | 200 | 5002
3008 | Julian Green | London | 300 | 5002
3004 | Fabian Johnson | Paris | 300 | 5006
3009 | Geoff Cameron | Berlin | 100 | 5003
3003 | Jozy Altidor | Moscow | 200 | 5007
3001 | Brad Guzan | London | | 5005
11. From the following table, write a SQL query to find orders that are
delivered by a salesperson with ID. 5001. Return ord_no, ord_date,
purch_amt.
12. From the following table, write a SQL query to find the Nobel Prize
winner(s) for the year 1970. Return year, subject and winner.
Sample table: nobel_win

YEAR SUBJECT WINNER COUNTRY CATEGORY
---- ------------------------- --------------------------------------------
1970 Physics Hannes Alfven Sweden Scientist
1970 Physics Louis Neel France Scientist
1970 Chemistry Luis Federico Leloir France Scientist
1970 Physiology Ulf von Euler Sweden Scientist
1970 Physiology Bernard Katz Germany Scientist
1970 Literature Aleksandr Solzhenitsyn Russia Linguist
1970 Economics Paul Samuelson USA Economist
1970 Physiology Julius Axelrod USA Scientist
1971 Physics Dennis Gabor Hungary Scientist
1971 Chemistry Gerhard Herzberg Germany Scientist
1971 Peace Willy Brandt Germany Chancellor
1971 Literature Pablo Neruda Chile Linguist
1971 Economics Simon Kuznets Russia Economist
1978 Peace Anwar al-Sadat Egypt President
1978 Peace Menachem Begin Israel Prime Minister
1987 Chemistry Donald J. Cram USA Scientist
1987 Chemistry Jean-Marie Lehn France Scientist
1987 Physiology Susumu Tonegawa Japan Scientist
1994 Economics Reinhard Selten Germany Economist
1994 Peace Yitzhak Rabin Israel Prime Minister
1987 Physics Johannes Georg Bednorz Germany Scientist
1987 Literature Joseph Brodsky Russia Linguist
1987 Economics Robert Solow USA Economist
1994 Literature Kenzaburo Oe Japan
13. From the following table, write a SQL query to find the Nobel Prize winner
in ‘Literature’ for 1970. Return winner.

14. From the following table, write a SQL query to locate the Nobel Prize
winner ‘Dennis Gabor'. Return year, subject.
15. From the following table, write a SQL query to find the Nobel Prize winners
in the field of ‘Physics’ since 1950. Return winner.
in ‘Chemistry’ between the years 1965 and 1975. Begin and end values are
included. Return year, subject, winner, and country.
17. Write a SQL query to display all details of the Prime Ministerial winners
after 1972 of Menachem Begin and Yitzhak Rabin.
18. From the following table, write a SQL query to retrieve the details of the
winners whose first names match with the string ‘Louis’. Return year, subject,
winner, country, and category.
19. From the following table, write a SQL query that combines the winners in
Physics, 1970 and in Economics, 1971. Return year, subject, winner, country,
and category.

in 1970 excluding the subjects of Physiology and Economics. Return year,
subject, winner, country, and category.
21. From the following table, write a SQL query to combine the winners in
'Physiology' before 1971 and winners in 'Peace' on or after 1974. Return year,
subject, winner, country, and category.
22. From the following table, write a SQL query to find the details of the Nobel
Prize winner 'Johannes Georg Bednorz'. Return year, subject, winner, country,
and category.
23. From the following table, write a SQL query to find Nobel Prize winners for
the subject that does not begin with the letter 'P'. Return year, subject, winner,
country, and category. Order the result by year, descending and winner in
ascending.
24. From the following table, write a SQL query to find the details of 1970
Nobel Prize winners. Order the results by subject, ascending except for
'Chemistry' and ‘Economics’ which will come at the end of the result set.
Return year, subject, winner, country, and category.
25. From the following table, write a SQL query to select a range of products
whose price is in the range Rs.200 to Rs.600. Begin and end values are
included. Return pro_id, pro_name, pro_price, and pro_com.
Sample table: item_mast

PRO_ID PRO_NAME PRO_PRICE PRO_COM
------- ------------------------- -------------- ----------
101 Mother Board 3200.00 15
102 Key Board 450.00 16
103 ZIP drive 250.00 14
104 Speaker 550.00 16
105 Monitor 5000.00 11
106 DVD drive 900.00 12
107 CD drive 800.00 12
108 Printer 2600.00 13
109 Refill cartridge 350.00 13
110 Mouse 250.00 12
26. From the following table, write a SQL query to calculate the average price
for a manufacturer code of 16. Return avg.
27. From the following table, write a SQL query to display the pro_name as
'Item Name' and pro_priceas 'Price in Rs.'
28. From the following table, write a SQL query to find the items whose prices
are higher than or equal to $250. Order the result by product price in
descending, then product name in ascending. Return pro_name and
pro_price.
29. From the following table, write a SQL query to calculate average price of
the items for each company. Return average price and company code.
30. From the following table, write a SQL query to find the cheapest item(s).
Return pro_name and, pro_price.

31. From the following table, write a SQL query to find the unique last name of
all employees. Return emp_lname.
Sample table: emp_details

EMP_IDNO EMP_FNAME EMP_LNAME EMP_DEPT
--------- --------------- --------------- ----------
127323 Michale Robbin 57
526689 Carlos Snares 63
843795 Enric Dosio 57
328717 Jhon Snares 63
444527 Joseph Dosni 47
659831 Zanifer Emily 47
847674 Kuleswar Sitaraman 57
748681 Henrey Gabriel 47
555935 Alex Manuel 57
539569 George Mardy 27
733843 Mario Saule 63
631548 Alan Snappy 27
839139 Maria Foster 57
32. From the following table, write a SQL query to find the details of
employees whose last name is 'Snares'. Return emp_idno, emp_fname,
emp_lname, and emp_dept.
employees who work in the department 57. Return emp_idno, emp_fname,
emp_lname and emp_dept..
My SQL Worksheet-12
1. From the following table, write a SQL query to locate the details of
customers with grade values above 100. Return customer_id, cust_name, city,
grade, and salesman_id.
2. From the following table, write a SQL query to find all the customers in ‘New
York’ city who have a grade value above 100. Return customer_id,
cust_name, city, grade, and salesman_id.
3. From the following table, write a SQL query to find customers who are from
the city of New York or have a grade of over 100. Return customer_id,
cust_name, city, grade, and salesman_id.
4. From the following table, write a SQL query to find customers who are
either from the city 'New York' or who do not have a grade greater than 100.
Return customer_id, cust_name, city, grade, and salesman_id.
5. From the following table, write a SQL query to identify customers who do
not belong to the city of 'New York' or have a grade value that exceeds 100.
Return customer_id, cust_name, city, grade, and salesman_id.
6. From the following table, write a SQL query to find details of all orders
excluding those with ord_date equal to '2012-09-10' and salesman_id higher
than 5005 or purch_amt greater than 1000.Return ord_no, purch_amt,
ord_date, customer_id and salesman_id.
Sample table : orders
7. From the following table, write a SQL query to find the details of those
salespeople whose commissions range from 0.10 to0.12. Return salesman_id,
name, city, and commission.
Sample table : salesman
8. From the following table, write a SQL query to find details of all orders with
a purchase amount less than 200 or exclude orders with an order date greater
than or equal to '2012-02-10' and a customer ID less than 3009. Return
ord_no, purch_amt, ord_date, customer_id and salesman_id.
9. From the following table, write a SQL query to find all orders that meet the
following conditions. Exclude combinations of order date equal to '2012-08-17'
or customer ID greater than 3005 and purchase amount less than 1000.
10. Write a SQL query that displays order number, purchase amount, and the
achieved and unachieved percentage (%) for those orders that exceed 50% of
the target value of 6000.
11. From the following table, write a SQL query to find the details of all
employees whose last name is ‘Dosni’ or ‘Mardy’. Return emp_idno,
emp_fname, emp_lname, and emp_dept.
Sample table : emp_details
12. From the following table, write a SQL query to find the employees who
work at depart 47 or 63. Return emp_idno, emp_fname, emp_lname, and
emp_dept.
Sample table : emp_details
My SQL Worksheet-13
salespeople who come from the 'Paris' City or 'Rome' City. Return
salesman_id, name, city, commission
Sample Output:
salesman_id name city commission
5002 Nail Knite Paris 0.13
5006 Mc Lyon Paris 0.14
5007 Paul Adam Rome 0.13
2. From the following table, write a SQL query to find the details of the
salespeople who come from either 'Paris' or 'Rome'. Return salesman_id,
name, city, commission.
Sample Output:
salespeople who live in cities other than Paris and Rome. Return
salesman_id, name, city, commission.
Sample Output:
5001 James Hoog New York 0.15
5005 Pit Alex London 0.11
5003 Lauson Hen San Jose 0.12
4. From the following table, write a SQL query to retrieve the details of all
customers whose ID belongs to any of the values 3007, 3008 or 3009. Return
customer_id, cust_name, city, grade, and salesman_id.
Sample Output:
customer_id cust_name city grade salesman_id
3007 Brad Davis New York 200 5001
3008 Julian Green London 300 5002
3009 Geoff Cameron Berlin 100 5003
5. From the following table, write a SQL query to find salespeople who receive
commissions between 0.12 and 0.14 (begin and end values are included).
Return salesman_id, name, city, and commission.
Sample Output:
5003 Lauson Hen San Jose0.12
6. From the following table, write a SQL query to select orders between 500
and 4000 (begin and end values are included). Exclude orders amount 948.50
and 1983.43. Return ord_no, purch_amt, ord_date, customer_id, and
salesman_id.
Sample Output:
70005 2400.60 2012-07-27 3007 5001
70003 2480.40 2012-10-10 3009 5003
70013 3045.60 2012-04-25 3002 5001
salespeople whose names begin with any letter between 'A' and 'L' (not
inclusive). Return salesman_id, name, city, commission.
Sample Output:
5001 James Hoog New York 0.15
8. From the following table, write a SQL query to find the details of all
salespeople except those whose names begin with any letter between 'A' and
'L' (not inclusive). Return salesman_id, name, city, commission.
Sample Output:
5005 Pit Alex London 0.11
5003 Lauson Hen San Jose 0.12
customers whose names begins with the letter 'B'. Return customer_id,
cust_name, city, grade, salesman_id..
Sample Output:
3001 Brad Guzan London 5005
10. From the following table, write a SQL query to find the details of the
customers whose names end with the letter 'n'. Return customer_id,
cust_name, city, grade, salesman_id.
Sample Output:
3004 Fabian Johnson Paris 300 5006
salespeople whose names begin with ‘N’ and the fourth character is 'l'. Rests
may be any character. Return salesman_id, name, city, commission.
Sample table : salesman
Sample Output:
12. From the following table, write a SQL query to find those rows where col1
contains the escape character underscore ( _ ). Return col1.
Sample table: testtable
Sample Output:
col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300
13. From the following table, write a SQL query to identify those rows where
col1 does not contain the escape character underscore ( _ ). Return col1.
Sample Output:
col1
A001/DJ-402%45\2015/200
A001/DJ-402\44
14. From the following table, write a SQL query to find rows in which col1
contains the forward slash character ( / ). Return col1.
Sample Output:
col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ-402%45\2015/200
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300
A001/DJ-402\44
15. From the following table, write a SQL query to identify those rows where
col1 does not contain the forward slash character ( / ). Return col1.
Sample Output:
col1
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
contains the string ( _/ ). Return col1.
Sample Output:
col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015
does not contain the string ( _/ ). Return col1.
Sample Output:
col1
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ-402%45\2015/200
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300
A001/DJ-402\44
contains the character percent ( % ). Return col1.
Sample Output:
col1
A001/DJ-402%45\2015/200
A001/DJ_402\45\2015%100
A001/DJ_402%45\2015/300
does not contain the character percent ( % ). Return col1.
Sample Output:
col1
A001/DJ-402\44_/100/2015
A001_\DJ-402\44_/100/2015
A001_DJ-402-2014-2015
A002_DJ-401-2014-2015
A001/DJ_401
A001/DJ_402\44
A001/DJ_402\44\2015
A001/DJ-402\44
20. From the following table, write a SQL query to find all those customers
who does not have any grade. Return customer_id, cust_name, city, grade,
salesman_id.
Sample Output:
21. From the following table, write a SQL query to locate all customers with a
grade value. Return customer_id, cust_name,city, grade, salesman_id.
Sample Output:
3002 Nick Rimando New York 100 5001
3005 Graham Zusi California 200 5002
3004 Fabian Johnson Paris 300 5006
3003 Jozy Altidor Moscow 200 5007
22. From the following table, write a SQL query to locate the employees
whose last name begins with the letter 'D'. Return emp_idno, emp_fname,
emp_lname and emp_dept.
Sample Output:
emp_idno emp_fname emp_lname emp_dept
843795 Enric Dosio 57
444527 Joseph Dosni 47
My SQL Worksheet-14
1. From the following table, write a SQL query to calculate total purchase
amount of all orders. Return total purchase amount.
Sample Output:
sum
17541.18
2. From the following table, write a SQL query to calculate the average
purchase amount of all orders. Return average purchase amount.
Sample Output:
avg
1461.7650000000000000
3. From the following table, write a SQL query that counts the number of
unique salespeople. Return number of salespeople.
Sample Output:
count
6
4. From the following table, write a SQL query to count the number of
customers. Return number of customers.
Sample Output:
count
8
5. From the following table, write a SQL query to determine the number of
customers who received at least one grade for their activity.
Sample Output:
count
7
6. From the following table, write a SQL query to find the maximum purchase
amount.

Sample Output:
max
5760.00
7. From the following table, write a SQL query to find the minimum purchase
amount.
Sample Output:
min
65.26
8. From the following table, write a SQL query to find the highest grade of the
customers in each city. Return city, maximum grade.
Sample Output:
city max
London 300
Paris 300
New York 200
California 200
Berlin 100
Moscow 200
9. From the following table, write a SQL query to find the highest purchase
amount ordered by each customer. Return customer ID, maximum purchase
amount.
Sample Output:
customer_id max
3007 2400.60
3008 250.45
3002 5760.00
3001 270.65
3009 2480.40
3004 1983.43
3003 75.29
3005 948.50
10. From the following table, write a SQL query to find the highest purchase
amount ordered by each customer on a particular date. Return, order date and
highest purchase amount. ;
Sample Output:
customer_id ord_date max
3002 2012-10-05 65.26
3003 2012-08-17 75.29
3005 2012-10-05 150.50
3007 2012-07-27 2400.60
3009 2012-08-17 110.50
3001 2012-09-10 270.65
3002 2012-09-10 5760.00
3005 2012-09-10 948.50
3009 2012-10-10 2480.40
3008 2012-06-27 250.45
3004 2012-10-10 1983.43
3002 2012-04-25 3045.60
11. From the following table, write a SQL query to determine the highest
purchase amount made by each salesperson on '2012-08-17'. Return
salesperson ID, purchase amount
Sample Output:
salesman_id max
5003 110.50
5007 75.29
12. From the following table, write a SQL query to find the highest order
(purchase) amount by each customer on a particular order date. Filter the
result by highest order (purchase) amount above 2000.00. Return customer
id, order date and maximum purchase amount.
Sample Output:
3007 2012-07-27 2400.60
3002 2012-09-10 5760.00
3009 2012-10-10 2480.40
3002 2012-04-25 3045.60
13. From the following table, write a SQL query to find the maximum order
(purchase) amount in the range 2000 - 6000 (Begin and end values are
included.) by combination of each customer and order date. Return customer
id, order date and maximum purchase amount.
Sample Output:
3007 2012-07-27 2400.60
3002 2012-09-10 5760.00
3009 2012-10-10 2480.40
3002 2012-04-25 3045.60
(purchase) amount based on the combination of each customer and order
date. Filter the rows for maximum order (purchase) amount is either 2000,
3000, 5760, 6000. Return customer id, order date and maximum purchase
amount.
Sample Output:
3002 2012-09-10 5760.00
15. From the following table, write a SQL query to determine the maximum
order amount for each customer. The customer ID should be in the range
3002 and 3007(Begin and end values are included.). Return customer id and
maximum purchase amount.
Sample Output:
customer_id max
3002 5760.00
3007 2400.60
3004 1983.43
3003 75.29
3005 948.50
(purchase) amount for each customer. The customer ID should be in the
range 3002 and 3007(Begin and end values are included.). Filter the rows for
maximum order (purchase) amount is higher than 1000. Return customer id
and maximum purchase amount.
Sample Output:
customer_id max
3002 5760.00
3007 2400.60
3004 1983.43
17. From the following table, write a SQL query to determine the maximum
order (purchase) amount generated by each salesperson. Filter the rows for
the salesperson ID is in the range 5003 and 5008 (Begin and end values are
included.). Return salesperson id and maximum purchase amount.
Sample Output:
salesman_id max
5005 270.65
5003 2480.40
5007 75.29
5006 1983.43
18. From the following table, write a SQL query to count all the orders
generated on '2012-08-17'. Return number of orders.
Sample Output:
count
2
salespeople in a city. Return number of salespeople.
Sample Output:
count
6
20. From the following table, write a SQL query to count the number of orders
based on the combination of each order date and salesperson. Return order
date, salesperson id.
Sample Output:
ord_date salesman_id count
2012-07-27 5001 1
2012-08-17 5007 1
2012-04-25 5001 1
2012-09-10 5002 1
2012-10-05 5002 1
2012-10-10 5003 1
2012-09-10 5005 1
2012-08-17 5003 1
2012-06-27 5002 1
2012-09-10 5001 1
2012-10-05 5001 1
2012-10-10 5006 1
21. From the following table, write a SQL query to calculate the average
product price. Return average product price.
Sample Output:
Average Price
1435.0000000000000000
products whose price are higher than or equal to 350. Return number of
products.
Sample Output:
Number of Products
8
23. From the following table, write a SQL query to compute the average price
for unique companies. Return average price and company id.
Sample Output:
Average Price Company ID
250.0000000000000000 14
650.0000000000000000 12
3200.0000000000000000 15
5000.0000000000000000 11
1475.0000000000000000 13
500.0000000000000000 16
24. From the following table, write a SQL query to compute the sum of the
allotment amount of all departments. Return sum of the allotment amount.
Sample table: emp_department
Sample Output:
sum
450000
with results
employees in each department. Return department code and number of
employees.
Sample Output:
emp_dept count
27 2
57 5
47 3
63 3
My SQL Worksheet-15
1. From the following table, write a SQL query to select all the salespeople.
Return salesman_id, name, city, commission with the percent sign (%).
Sample Output:
salesman_id name city ?column? ?column?
5001 James Hoog New York % 15.00
5002 Nail Knite Paris % 13.00
5005 Pit Alex London % 11.00
5006 Mc Lyon Paris % 14.00
5007 Paul Adam Rome % 13.00
5003 Lauson Hen San Jose % 12.00
2. From the following table, write a SQL query to find the number of orders
booked for each day. Return the result in a format like "For 2001-10-10 there
are 15 orders".".
Sample Output:
?column? ord_date ?column? count ?column?
For 2012-04-25 ,there are 1 orders.
3. From the following table, write a SQL query to find all the orders. Sort the
result-set in ascending order by ord_no. Return all fields.
Sample Output:
70001 150.50 2012-10-05 3005 5002
70002 65.26 2012-10-05 3002 5001
70003 2480.40 2012-10-10 3009 5003
70004 110.50 2012-08-17 3009 5003
70005 2400.60 2012-07-27 3007 5001
70007 948.50 2012-09-10 3005 5002
70008 5760.00 2012-09-10 3002 5001
70009 270.65 2012-09-10 3001 5005
70010 1983.43 2012-10-10 3004 5006
70011 75.29 2012-08-17 3003 5007
70012 250.45 2012-06-27 3008 5002
70013 3045.60 2012-04-25 3002 5001
result-set in descending order by ord_date. Return all fields.

Sample Output:
70010 1983.43 2012-10-10 3004 5006
70003 2480.40 2012-10-10 3009 5003
70002 65.26 2012-10-05 3002 5001
70001 150.50 2012-10-05 3005 5002
70009 270.65 2012-09-10 3001 5005
70008 5760.00 2012-09-10 3002 5001
70007 948.50 2012-09-10 3005 5002
70011 75.29 2012-08-17 3003 5007
70004 110.50 2012-08-17 3009 5003
70005 2400.60 2012-07-27 3007 5001
70012 250.45 2012-06-27 3008 5002
70013 3045.60 2012-04-25 3002 5001
result-set in descending order by ord_date and purch_amt. Return all fields.
Sample Output:
70013 3045.60 2012-04-25 3002 5001
70012 250.45 2012-06-27 3008 5002
70005 2400.60 2012-07-27 3007 5001
70004 110.50 2012-08-17 3009 5003
70011 75.29 2012-08-17 3003 5007
70008 5760.00 2012-09-10 3002 5001
70007 948.50 2012-09-10 3005 5002
70009 270.65 2012-09-10 3001 5005
70001 150.50 2012-10-05 3005 5002
70002 65.26 2012-10-05 3002 5001
70003 2480.40 2012-10-10 3009 5003
70010 1983.43 2012-10-10 3004 5006
6. From the following table, write a SQL query to find all the customers. Sort
the result-set by customer_id. Return cust_name, city, grade.
Sample Output:
cust_name city grade
Brad Guzan London
Nick Rimando New York 100
Jozy Altidor Moscow 200
Fabian Johnson Paris 300
Graham Zusi California 200
Brad Davis New York 200
Julian Green London 300
Geoff Cameron Berlin 100
7. From the following table, write a SQL query that calculates the maximum
purchase amount generated by each salesperson for each order date. Sort
the result-set by salesperson id and order date in ascending order. Return
salesperson id, order date and maximum purchase amount.
Sample Output:
salesman_id ord_date max
5001 2012-04-25 3045.60
5001 2012-07-27 2400.60
5001 2012-09-10 5760.00
5001 2012-10-05 65.26
5002 2012-06-27 250.45
5002 2012-09-10 948.50
5002 2012-10-05 150.50
5003 2012-08-17 110.50
5003 2012-10-10 2480.40
5005 2012-09-10 270.65
5006 2012-10-10 1983.43
5007 2012-08-17 75.29
8. From the following table, write a SQL query to find all the customers. Sort
the result-set in descending order on 3rd field. Return customer name, city
and grade.
Sample Output:
cust_name city grade
Brad Guzan London
Fabian Johnson Paris 300
Julian Green London 300
Brad Davis New York 200
Jozy Altidor Moscow 200
Graham Zusi California 200
Nick Rimando New York 100
Geoff Cameron Berlin 100
9. From the following table, write a SQL query that counts the unique orders
and the highest purchase amount for each customer. Sort the result-set in
descending order on 2nd field. Return customer ID, number of distinct orders
and highest purchase amount by each customer.
Sample Output:
customer_id count max
3002 3 5760.00
3009 2 2480.40
3005 2 948.50
3004 1 1983.43
3001 1 270.65
3007 1 2400.60
3008 1 250.45
3003 1 75.29
10. From the following table, write a SQL query to calculate the summation of
purchase amount, total commission (15% for all salespeople) by each order
date. Sort the result-set on order date. Return order date, summation of
purchase amount and commission.
Sample Output:
ord_date sum ?column?
2012-04-25 3045.60 456.8400
2012-06-27 250.45 37.5675
2012-07-27 2400.60 360.0900
2012-08-17 185.79 27.8685
2012-09-10 6979.15 1046.8725
2012-10-05 215.76 32.3640
2012-10-10 4463.83 669.5745
My SQL Worksheet-16
1. From the following tables, write a SQL query to find the salespeople and
customers who live in the same city. Return customer name, salesperson
name and salesperson city.
Sample Output:
cust_name name city
Nick Rimando James Hoog New York
Brad Davis James Hoog New York
Julian Green Pit Alex London
Fabian Johnson Mc Lyon Paris
Fabian Johnson Nail Knite Paris
Brad Guzan Pit Alex London
2. From the following tables, write a SQL query to locate all the customers and
the salesperson who works for them. Return customer name, and salesperson
name.
Sample Output:
cust_name name
Nick Rimando James Hoog
Brad Davis James Hoog
Graham Zusi Nail Knite
Julian Green Nail Knite
Fabian Johnson Mc Lyon
Geoff Cameron Lauson Hen
Jozy Altidor Paul Adam
Brad Guzan Pit Alex
3. From the following tables, write a SQL query to find those salespeople who
generated orders for their customers but are not located in the same city.
Return ord_no, cust_name, customer_id (orders table), salesman_id (orders
table).
Sample Output:
ord_no cust_name customer_id salesman_id
70004 Geoff Cameron 3009 5003
70003 Geoff Cameron 3009 5003
70011 Jozy Altidor 3003 5007
70001 Graham Zusi 3005 5002
70007 Graham Zusi 3005 5002
70012 Julian Green 3008 5002
4. From the following tables, write a SQL query to locate the orders made by
customers. Return order number, customer name.
Sample Output:
ord_no cust_name
70009 Brad Guzan
70002 Nick Rimando
70004 Geoff Cameron
70005 Brad Davis
70008 Nick Rimando
70010 Fabian Johnson
70003 Geoff Cameron
70011 Jozy Altidor
70013 Nick Rimando
70001 Graham Zusi
70007 Graham Zusi
70012 Julian Green
5. From the following tables, write a SQL query to find those customers where
each customer has a grade and is served by a salesperson who belongs to a
city. Return cust_name as "Customer", grade as "Grade".
Sample Output:
Customer |Grade|Order No|
--------------|-----|--------|
Nick Rimando | 100| 70002|
Geoff Cameron | 100| 70004|
Brad Davis | 200| 70005|
Fabian Johnson| 300| 70010|
Geoff Cameron | 100| 70003|
Jozy Altidor | 200| 70011|
Graham Zusi | 200| 70001|
Graham Zusi | 200| 70007|
Julian Green | 300| 70012|
6. From the following table, write a SQL query to find those customers who
are served by a salesperson and the salesperson earns commission in the
range of 12% to 14% (Begin and end values are included.). Return cust_name
AS "Customer", city AS "City".

Sample Output:
Customer City Salesman commission
Graham Zusi California Nail Knite 0.13
Julian Green London Nail Knite 0.13
Fabian Johnson Paris Mc Lyon 0.14
Geoff Cameron Berlin Lauson Hen 0.12
Jozy Altidor Moscow Paul Adam 0.13
7. From the following tables, write a SQL query to find all orders executed by
the salesperson and ordered by the customer whose grade is greater than or
equal to 200. Compute purch_amt*commission as “Commission”. Return
customer name, commission as “Commission%” and Commission.
Sample Output:
ord_no cust_name Commission% Commission
70005 Brad Davis 0.15 360.0900
70010 Fabian Johnson 0.14 277.6802
70011 Jozy Altidor 0.13 9.7877
70001 Graham Zusi 0.13 19.5650
70007 Graham Zusi 0.13 123.3050
70012 Julian Green 0.13 32.5585
8. From the following table, write a SQL query to find those customers who
placed orders on October 5, 2012. Return customer_id, cust_name, city,
grade, salesman_id, ord_no, purch_amt, ord_date, customer_id and
salesman_id.
Sample Output:
3002 Nick Rimando New York 100 5001 70002
65.26 2012-10-05 3002 5001
3005 Graham Zusi California 200 5002 70001
150.50 2012-10-05 3005 5002
My SQL Worksheet-17
1. From the following table, write a SQL query to find those employees whose
salaries are less than 6000. Return full name (first and last name), and
salary.
Sample table: employees
Sample Output:
full_name | salary
-------------------+---------
David Austin | 4800.00
Valli Pataballa | 4800.00
Diana Lorentz | 4200.00
Alexander Khoo | 3100.00
.....
(50 rows)
salary is higher than 8000. Return first name, last name and department
number and salary.

Sample Output:
first_name | last_name | department_id | salary
------------+------------+---------------+----------
Steven | King | 90 | 24000.00
Neena | Kochhar | 90 | 17000.00
Lex | De Haan | 90 | 17000.00
Alexander | Hunold | 60 | 9000.00
.....
(33 rows)
last name is "McEwen". Return first name, last name and department ID.
Sample Output:
first_name | last_name | department_id
------------+-----------+---------------
Allan | McEwen | 80
(1 row)
4. From the following table, write a SQL query to identify employees who do
not have a department number. Return employee_id, first_name, last_name,
email, phone_number, hire_date, job_id, salary,commission_pct, manager_id
and department_id.
Sample Output:
employee_id | first_name | last_name | email | phone_number |
hire_date | job_id | salary | commission_pct | manager_id |
department_id
-------------+------------+-----------+-------+--------------
+-----------+--------+--------+----------------+------------+-
------------
(0 rows)
5. From the following table, write a SQL query to find the details of 'Marketing'
department. Return all fields.
Sample table: departments
Sample Output:
department_id | department_name | manager_id | location_id
---------------+-----------------+------------+-------------
20 | Marketing | 201 | 1800
(1 row)
first name does not contain the letter ‘M’. Sort the result-set in ascending order
by department ID. Return full name (first and last name together), hire_date,
salary and department_id.
Sample Output:
full_name | hire_date | salary | department_id
-------------------+------------+----------+---------------
Kimberely Grant | 2007-05-24 | 7000.00 | 0
Jennifer Whalen | 2003-09-17 | 4400.00 | 10
Pat Fay | 2005-08-17 | 6000.00 | 20
Guy Himuro | 2006-11-15 | 2600.00 | 30
.....
(100 rows)
7. From the following table, write a SQL query to find those employees who
earn between 8000 and 12000 (Begin and end values are included.) and get
some commission. These employees joined before ‘1987-06-05’ and were not
included in the department numbers 40, 120 and 70. Return all fields.
Sample Output:
employee_id | first_name | last_name | email |
phone_number | hire_date | job_id | salary |
commission_pct | manager_id | department_id
-------------+-------------+------------+----------+----------
----------+------------+------------+----------+--------------
--+------------+---------------
103 | Alexander | Hunold | AHUNOLD |
590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 |
0.00 | 102 | 60
108 | Nancy | Greenberg | NGREENBE |
515.124.4569 | 2002-08-17 | FI_MGR | 12000.00 |
0.00 | 101 | 100
109 | Daniel | Faviet | DFAVIET |
515.124.4169 | 2002-08-16 | FI_ACCOUNT | 9000.00 |
0.00 | 108 | 100
110 | John | Chen | JCHEN |
515.124.4269 | 2005-09-28 | FI_ACCOUNT | 8200.00 |
0.00 | 108 | 100
.....
(30 rows)
8. From the following table, write a SQL query to find those employees who do
not earn any commission. Return full name (first and last name), and salary.
Sample Output:
full_name | salary
-----------+--------
(0 rows)
9. From the following table, write a SQL query to find the employees whose
salary is in the range 9000,17000 (Begin and end values are included). Return
full name, contact details and salary.
Sample Output:
full_name | contact_details |
remuneration
-------------------+-------------------------------+----------
----
Neena Kochhar | 515.123.4568 - NKOCHHAR |
17000.00
Lex De Haan | 515.123.4569 - LDEHAAN |
17000.00
Alexander Hunold | 590.423.4567 - AHUNOLD |
9000.00
Nancy Greenberg | 515.124.4569 - NGREENBE |
12000.00
.....
(26 rows)
first name ends with the letter ‘m’. Return the first and last name, and salary.
Sample Output:
first_name | last_name | salary
------------+-----------+---------
Adam | Fripp | 8200.00
Payam | Kaufling | 7900.00
William | Smith | 7400.00
William | Gietz | 8300.00
(4 rows)
11. From the following table, write a SQL query to find those employees
whose salaries are not between 7000 and 15000 (Begin and end values are
included). Sort the result-set in ascending order by the full name (first and
last). Return full name and salary.
Sample Output:
name | salary
-------------------+----------
Alana Walsh | 3100.00
Alexander Khoo | 3100.00
Alexis Bull | 4100.00
Amit Banda | 6200.00
.....
(63 rows)
were hired between November 5th, 2007 and July 5th, 2009. Return full name
(first and last), job id and hire date.
Sample Output:
full_name | job_id | hire_date
------------------+------------+------------
Luis Popp | FI_ACCOUNT | 2007-12-07
Kevin Mourgos | ST_MAN | 2007-11-16
Steven Markle | ST_CLERK | 2008-03-08
Ki Gee | ST_CLERK | 2007-12-12
.....
(16 rows)
work either in department 70 or 90. Return full name (first and last name),
department id.
Sample Output:
full_name | department_id
---------------+---------------
Steven King | 90
Neena Kochhar | 90
Lex De Haan | 90
Hermann Baer | 70
(4 rows)
work under a manager. Return full name (first and last name), salary, and
manager ID.
Sample Output:
full_name | salary | manager_id
-------------------+----------+------------
Neena Kochhar | 17000.00 | 100
Lex De Haan | 17000.00 | 100
Alexander Hunold | 9000.00 | 102
Bruce Ernst | 6000.00 | 103
.....
15. From the following table, write a SQL query to find the employees who
were hired before June 21st, 2002. Return all fields.
Sample Output:
employee_id | first_name | last_name | email | phone_number
| hire_date | job_id | salary | commission_pct |
manager_id | department_id
-------------+------------+-----------+----------+------------
--+------------+------------+----------+----------------+-----
-------+---------------
102 | Lex | De Haan | LDEHAAN |
515.123.4569 | 2001-01-13 | AD_VP | 17000.00 |
0.00 | 100 | 90
203 | Susan | Mavris | SMAVRIS |
515.123.7777 | 2002-06-07 | HR_REP | 6500.00 |
0.00 | 101 | 40
204 | Hermann | Baer | HBAER |
515.123.8888 | 2002-06-07 | PR_REP | 10000.00 |
0.00 | 101 | 70
205 | Shelley | Higgins | SHIGGINS |
515.123.8080 | 2002-06-07 | AC_MGR | 12000.00 |
0.00 | 101 | 110
206 | William | Gietz | WGIETZ |
515.123.8181 | 2002-06-07 | AC_ACCOUNT | 8300.00 |
0.00 | 205 | 110
(5 rows)
managers hold the ID 120, 103, or 145. Return first name, last name, email,
salary and manager ID.
Sample Output:
first_name | last_name | email | salary | manager_id
-------------+-------------+----------+----------+------------
Bruce | Ernst | BERNST | 6000.00 | 103
David | Austin | DAUSTIN | 4800.00 | 103
Valli | Pataballa | VPATABAL | 4800.00 | 103
Diana | Lorentz | DLORENTZ | 4200.00 | 103
.....
(18 rows)
17. From the following table, write a SQL query to find employees whose first
names contain the letters D, S, or N. Sort the result-set in descending order by
salary. Return all fields.
Sample Output:
-------------+------------+-----------+----------+------------
--------+------------+------------+----------+----------------
+------------+---------------
100 | Steven | King | SKING |
515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 |
0.00 | 0 | 90
101 | Neena | Kochhar | NKOCHHAR |
515.123.4568 | 2005-09-21 | AD_VP | 17000.00 |
0.00 | 100 | 90
205 | Shelley | Higgins | SHIGGINS |
515.123.8080 | 2002-06-07 | AC_MGR | 12000.00 |
0.00 | 101 | 110
108 | Nancy | Greenberg | NGREENBE |
515.124.4569 | 2002-08-17 | FI_MGR | 12000.00 |
0.00 | 101 | 100
.....
(26 rows)
earn above 11000 or the seventh character in their phone number is 3. Sort
the result-set in descending order by first name. Return full name (first name
and last name), hire date, commission percentage, email, and telephone
separated by '-', and salary.

Sample Output:
full_name | hire_date | commission_pct |
contact_details | salary
-------------------+------------+----------------+------------
--------------------+----------
William Gietz | 2002-06-07 | 0.00 | WGIETZ -
515.123.8181 | 8300.00
Valli Pataballa | 2006-02-05 | 0.00 | VPATABAL -
590.423.4560 | 4800.00
Susan Mavris | 2002-06-07 | 0.00 | SMAVRIS -
515.123.7777 | 6500.00
Steven King | 2003-06-17 | 0.00 | SKING -
515.123.4567 | 24000.00
.....
(25 rows)
19. From the following table, write a SQL query to find those employees
whose first name contains a character 's' in the third position. Return first
name, last name and department id.
Sample table : employees
Sample Output:
first_name | last_name | department_id
-------------+-----------+---------------
Jose Manuel | Urman | 100
Jason | Mallin | 50
Joshua | Patel | 50
Lisa | Ozer | 80
Susan | Mavris | 40
(5 rows)
20. From the following table, write a SQL query to find those employees work
in the departments that are not part of the department 50 or 30 or 80. Return
employee_id, first_name,job_id, department_id.
Sample Output:
employee_id | first_name | job_id | department_id
-------------+-------------+------------+---------------
100 | Steven | AD_PRES | 90
101 | Neena | AD_VP | 90
102 | Lex | AD_VP | 90
103 | Alexander | IT_PROG | 60
104 | Bruce | IT_PROG | 60
.....
(22 rows)
department numbers are included in 30, 40, or 90. Return employee id, first
name, job id, department id.
Sample Output:
employee_id | first_name | job_id | department_id
-------------+------------+----------+---------------
100 | Steven | AD_PRES | 90
101 | Neena | AD_VP | 90
102 | Lex | AD_VP | 90
114 | Den | PU_MAN | 30
.....
(10 rows)
worked more than two jobs in the past. Return employee id.
Sample table : job_history
Sample Output:
employee_id
-------------
101
176
200
(3 rows)
employees, the sum of all salary, and difference between the highest salary
and lowest salaries by each job id. Return job_id, count, sum,
salary_difference.
Sample Output:
job_id | count | sum | salary_difference
------------+-------+-----------+-------------------
AC_ACCOUNT | 1 | 8300.00 | 0.00
ST_MAN | 5 | 36400.00 | 2400.00
IT_PROG | 5 | 28800.00 | 4800.00
SA_MAN | 5 | 61000.00 | 3500.00
AD_PRES | 1 | 24000.00 | 0.00
.....
(19 rows)
24. From the following table, write a SQL query to find each job ids where two
or more employees worked for more than 300 days. Return job id.
Sample Output:
job_id
------------
AC_ACCOUNT
ST_CLERK
(2 rows)
25. From the following table, write a SQL query to count the number of cities
in each country. Return country ID and number of cities.
Sample table : locations
Sample Output:
country_id | count
------------+-------
CH | 2
MX | 1
US | 4
AU | 1
IT | 2
.....
(15 rows)
employees worked under each manager. Return manager ID and number of
employees.
Sample Output:
manager_id | count
------------+-------
205 | 1
122 | 8
120 | 8
101 | 5
103 | 4
.....
(19 rows)
27. From the following table, write a SQL query to find all jobs. Sort the result-
set in descending order by job title. Return all fields.
Sample table : jobs
Sample Output:
job_id |job_title
|min_salary|max_salary|
----------|-------------------------------|----------|--------
--|
ST_MAN |Stock Manager | 5500|
8500|
ST_CLERK |Stock Clerk | 2000|
5000|
SH_CLERK |Shipping Clerk | 2500|
5500|
SA_REP |Sales Representative | 6000|
12000|
.....
28. From the following table, write a SQL query to find all those employees
who are either Sales Representatives or Salesmen. Return first name, last
name and hire date.
Sample Output:
first_name | last_name | hire_date
-------------+------------+------------
John | Russell | 2004-10-01
Karen | Partners | 2005-01-05
Alberto | Errazuriz | 2005-03-10
Gerald | Cambrault | 2007-10-15
Eleni | Zlotkey | 2008-01-29
.....
(35 rows)
29. From the following table, write a SQL query to calculate the average salary
of employees who receive a commission percentage for each department.
Return department id, average salary.
Sample Output:
department_id | avg
---------------+------------------------
90 | 19333.333333333333
20 | 9500.0000000000000000
100 | 8600.0000000000000000
40 | 6500.0000000000000000
.....
(12 rows)
30. From the following table, write a SQL query to find the departments where
any manager manages four or more employees. Return department_id.
Sample Output:
department_id
---------------
80
50
60
100
30
(5 rows)
31. From the following table, write a SQL query to find the departments where
more than ten employees receive commissions. Return department id.
Sample Output:
department_id
---------------
80
50
(2 rows)
have completed their previous jobs. Return employee ID, end_date.
Sample Output:
employee_id | max
-------------+------------
101 | 2005-03-15
200 | 2006-12-31
176 | 2007-12-31
(7 rows)
do not have commission percentage and have salaries between 7000, 12000
(Begin and end values are included.) and who are employed in the
department number 50. Return all the fields of employees.

Sample Output:
employee_id | first_name | last_name | email | phone_number |
hire_date | job_id | salary | commission_pct | manager_id |
department_id
-------------+------------+-----------+-------+--------------
+-----------+--------+--------+----------------+------------+-
------------
(0 rows)
34. From the following table, write a SQL query to compute the average salary
of each job ID. Exclude those records where average salary is on or lower
than 8000. Return job ID, average salary.
Sample Output:
job_id | avg
------------+------------------------
AC_ACCOUNT | 8300.0000000000000000
SA_MAN | 12200.000000000000
AD_PRES | 24000.000000000000
AC_MGR | 12000.0000000000000000
.....
(10 rows)
35. From the following table, write a SQL query to find those job titles where
maximum salary falls between 12000 and 18000 (Begin and end values are
included.). Return job_title, max_salary-min_salary.
Sample table : jobs
Sample Output:
job_title | salary_differences
----------------------+--------------------
Finance Manager | 7800
Accounting Manager | 7800
Sales Representative | 6000
Purchasing Manager | 7000
Marketing Manager | 6000
(5 rows)
first or last name begins with 'D'. Return first name, last name.
Sample Output:
first_name | last_name
------------+-----------
Lex | De Haan
David | Austin
Diana | Lorentz
Daniel | Faviet
.....
(14 rows)
37. From the following table, write a SQL query to find details of those jobs
where the minimum salary exceeds 9000. Return all the fields of jobs.
Sample table : jobs
Sample Output:
job_id | job_title | min_salary |
max_salary
---------+-------------------------------+------------+-------
-----
AD_PRES | President | 20000 |
40000
AD_VP | Administration Vice President | 15000 |
30000
SA_MAN | Sales Manager | 10000 |
20000
(3 rows)
joined after 7th September 1987. Return all the fields.

Sample Output:
-------------+-------------+-------------+----------+---------
-----------+------------+------------+----------+-------------
---+------------+---------------
100 | Steven | King | SKING |
515.123.4567 | 2003-06-17 | AD_PRES | 24000.00 |
0.00 | 0 | 90
101 | Neena | Kochhar | NKOCHHAR |
515.123.4568 | 2005-09-21 | AD_VP | 17000.00 |
0.00 | 100 | 90
102 | Lex | De Haan | LDEHAAN |
515.123.4569 | 2001-01-13 | AD_VP | 17000.00 |
0.00 | 100 | 90
103 | Alexander | Hunold | AHUNOLD |
590.423.4567 | 2006-01-03 | IT_PROG | 9000.00 |
0.00 | 102 | 60
104 | Bruce | Ernst | BERNST |
590.423.4568 | 2007-05-21 | IT_PROG | 6000.00 |
0.00 | 103 | 60
(107 rows)

Mysql - WS 1 - 10

Uploaded by

Copyright:

Available Formats

Mysql - WS 1 - 10

Uploaded by

Document Information

Original Title

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

Mysql - WS 1 - 10

Uploaded by

Copyright:

Available Formats

MYSQL - WORKSHEET

2. Write MySql command will be used to open an already existing database

3. Write MySql command to open an existing database.

4. What does SQL stand for? What is MySQL?

5. Write two examples of DBMS software.

8. What is the relationship between SQL and MySQL ?

9. Mention any two example of common Database Management System.

10. Suggest Archana suitable command for the following purpose:

12. Write the command to create a new database “School”

One of the rows inserted is as follows :

18. suggest her suitable command for the following purpose:

3. Kuhu has already created a table ‘Hospital’ as shown below:

6. Kunal created the following table with the name ‘Friends’ :

2. Consider the table RESULT given below.

Write command to insert a new row

3. Consider the Table SHOPPE given below.

To insert a new row in the table Shoppe

4. How is NULL value different from 0 (Zero) value?

5. Consider the following table named "GYM"

6. What is meant by NULL value in MySQL?

Will she be able to successfully insert it ? Give reason.

12. Is NULL and 0(zero) same? Jusify your answer.

13. Write the full forms of the following:

2. In a database there are two tables "Product" as shown below :

5. Consider the following table named "GYM"

6. Write the UPDATE statement in MySQL to increase commission by 100.00 in the

7. Write two examples of DML commands of SQL.

9. Consider the following table named “GARMENT”.

10 In a Database, there are two tables given below :

13 Consider the following table:

Write commands in SQL to increase the price of “Amlodipine” by 50.

2. The LastName column of a table "Directory" is given below:

b)SELECT lastname FROM Directory WHERE lastname not like

i. To display all information about teachers of PGT category.

6. The Mname Column of a table Members is given below :

(i) To list the names of those students,

9. Mr. Janak is using a table with following columns :

(i) To display names of the items whose

(i) Display the SurNames, FirstNames

(i) To display the names of all the items whose name

(i) To display Accountno, Name and DateOfopen of

(i) Select Salary+100 from Employee where EmpId='A002';

i. To display list of all foreigner friends.

22. Consider the table ‘empsalary’.

23. Consider the table ‘Employee’.

28. Consider the following table:

32. Write the following statement using ‘OR’ logical operator :

3. State difference between date functions NOW( ) and SYSDATE( ) of MySql.

(i) SELECT ROUND(Balance,-3) FROM SBOP

6. Write the output of the following SQL queries :

i. Select ucase(concat(name,”*”,city)) from friends where country like ‘Denmark’;

12. Write the output of the following SQL queries:

(i) SELECT Name, LENGTH(Name) FROM

14. Identify Single Row functions of MySQL amongst the following :

(i) SELECT MID(Mname,1,2)from Gym;

16. Observe the given table named “Loan”

i. To count the number of teachers in English department.

3. "PrincipaiName" is a column in a table "Schools". The SOL queries

i. To count the number of projects of cost less than 100000.