PGDCA 106 Lab Practice - 2 (PC Skills & Database) Part III: SQL

8. SQL commands using where clause

The SQL statement SELECT is used for retrieving data or selecting records from tables stored in an
SQL compliant RDBMS. The simple syntax we discussed earlier can be extended to specify an
optional WHERE clause to filter rows displayed in the result. The extended syntax of SELECT
statement is

SELECT column_list
FROM table_name
WHERE condition

Q #20 ) Display the details of all students in class ‘C1’

SELECT * FROM student WHERE class = ‘C1’

Q #21 ) Display the names of all students in class ‘C1’

SELECT name FROM student WHERE class = ‘C1’

Q #22 ) Display the name, gender and date of births of all students who were born after 31-
December-2000.
SELECT name, gender, dob FROM student WHERE dob > ‘2000-12-31’

Q #23) Display the name, dob, doj and salary of all employees who draws salary less than 20,000
SELECT name, dob, doj, salaray FROM employee WHERE salary < 20000

10. SQL commands using Like, in

Q #24 ) Display the details of all students whose name starts with alphabet ‘A’
SELECT * FROM student WHERE name LIKE ‘A%’

Q #25 ) Display the names of all students whose name contains the alphabet ‘s’
SELECT name FROM student WHERE name LIKE ‘%s%’

Q #26 ) Display the name, gender and date of births of all students who were born in the years 2000
and 2001.
SELECT name, gender, dob
FROM student
WHERE year(dob) IN (2000,2001)

Q #27) Display the name, dob, doj and salary of all employees who have joined in the months
‘January’, ‘February’ or ‘March’
SELECT name, dob, doj, salaray
FROM employee
WHERE month(doj) IN (‘January’, ‘February’, ‘March’)

11. SQL commands using or, and, between, Null

Q #28 ) Display the name, gender and date of births of all students who were born in the years 2000
and 2001.
SELECT name, gender, dob
FROM student
WHERE year(dob) = 2000 OR year(dob) = 2001

Q #29) Display the name, dob, doj and salary of all employees who have joined in the months
‘January’, ‘February’ or ‘March’
 PGDCA 106 Lab Practice - 2 (PC Skills & Database) Part III: SQL
SELECT name, dob, doj, salaray
FROM employee
WHERE month(doj) = ‘January’
OR month(doj) = ‘February’
OR month(doj) = ‘March’

Q #30 ) Display the name, gender and date of births of all ‘Female’ students who were born after
31-December-2000.
SELECT name, gender, dob
FROM student
WHERE gender = ‘female’
AND dob > ‘2000-12-31’

Q #31) Display the name, dob, doj and salary of all employees who joined after ‘31-December-2020
and draws salary greater than 25,000
SELECT name, dob, doj, salaray
FROM employee
WHERE doj > ‘2020-12-31’
AND salary > 25000

Q #32 ) Display the name, gender and date of births of all students who were born after 31-
December-2000 and before 01-January-2002.
SELECT name, gender, dob
FROM student
WHERE dob > ‘2000-12-31’
AND dob < ‘2002-01-01’

Q #33) Display the name, dob, doj and salary of all employees who draws salary greater than
20,000 and less than 30,000
SELECT name, dob, doj, salaray
FROM employee
WHERE salary > 20000
AND salary < 30000

Q #34 ) Display the details of all students whose class is unspecified

SELECT * FROM student WHERE class IS NULL

Q #35 ) Display the names of all students whose class or mark is not specified
SELECT name FROM student WHERE class IS NULL OR mark IS NULL

12. SQL commands using Date functions, string functions

Q #36 ) Display the details of all students who were born on ‘Monday’s
SELECT * FROM student WHERE dayname(dob) = ‘Monday’

Q #37 ) Display the names of all students who have completed 18 years
SELECT name FROM student
WHERE datediff(curdate(), dob)/365.25 > 18

Q #38) Display the name, dob, doj and salary of all employees who got job before they complete 22
years
SELECT name, dob, doj, salaray
 PGDCA 106 Lab Practice - 2 (PC Skills & Database) Part III: SQL
FROM employee
WHERE datediff(doj, dob) < 22 * 365.25

Q #39) Display the name, dob, doj, salary, and date of retirement (last day of the month in which
they complete 60 years) of all employees
SELECT name, dob, doj, salaray,
LAST_DAY(DATE_ADD(dob, INTERVAL 1 YEAR)
FROM employee

Q #40 ) Display the details of all students who were born on the last day of the month in which they
were born
SELECT * FROM student WHERE day(dob) = day(last_day(dob))

Q #41 ) Display the names of all students in uppercase and lowercase

SELECT UCASE(name), LCASE(name) FROM student

13. SQL commands for deleting records, dropping tables

The SQL statement DELETE is used for deleting data from tables stored in an SQL compliant
RDBMS. The extended syntax of DELETE statement is

DELETE FROM table_name WHERE condition

Q #42 ) Delete all rows of student in the class ‘C1’ from the student table
DELETE FROM student WHERE class = ‘C1’

Q #43 ) Delete all employees who joined before ‘01-January-1970’ in the employee table
DELETE FROM employee WHERE doj < ‘1970-01-01’

The SQL statement DROP TABLE is used for deleting or removing the tables stored in an SQL
compliant RDBMS. This statement will permanently remove both the data and its structure/skeleton
from the datatbase.

DROP TABLE statement will remove a table from the database where as DELETE statement will
only remove some/all rows from a table.

This statement is discussed earlier. Refer Q#18 and Q#19.

14. SQL commands using constraints

We can include intergity constraints while creating a table, so that whenever we insert/update data
in the table the DBMS will check whether the data inserted/updated into the table satisifes all the
integrity constraints defined for that table and if not it will reject the request and gives appropriate
error messages.

Q #44) Create a table student with the following structure

Rollno INTEGER primary key, Name VARCHAR(40), Dob DATE must be greater than 31-12-
2005, Gender CHAR(6), Class CHAR(2) must refer to the primary key of classes table, mark
TINYINT default 0.

CREATE TABLE students (

rollno int NOT NULL,
 PGDCA 106 Lab Practice - 2 (PC Skills & Database) Part III: SQL
name varchar(40) NOT NULL,
dob DATE CHECK(dob > ‘2005-12-31’),
gender CHAR(6),
class varchar(2),
mark TINYINT DEFAULT 0,
PRIMARY KEY (rollno),
FOREIGN KEY (class) REFERENCES classes(class_code)
)

15. Commands to access records from multiple tables

Q #45 ) Display the employee names and department names of all employees
SELECT employee.name, departments.name
FROM employee, department
WHERE employee.department_code = department.code

16. SQL commands for creating views, using join

Q #46 ) Create view containing the details of all students in class ‘C1’
CREATE VIEW c1students AS
SELECT * FROM students WHERE class = ‘C1’

Q #47 ) Create a view containing the classes, number of students, average mark
SELECT class, COUNT(name), AVG(mark)
FROM student
GROUP BY class

Q #48 ) Create a view with name, gender and date of births of all students who were born after 31-
December-2000.
CREATE VIEW studentsabove21 AS
SELECT name, gender, dob FROM student WHERE dob > ‘2000-12-31’

Q #49) Create a view with name, dob, doj and salary of all employees who draws salary less than
20,000
CREATE VIEW employee2 AS
SELECT name, dob, doj, salaray FROM employee WHERE salary < 20000

17. SQL commands for ordering records, grouping records

The SQL statement SELECT is used for retrieving data or selecting records from tables stored in an
SQL compliant RDBMS. The syntax with the optional WHERE clause to filter rows displayed in
the result, can be extended further to include an ORDER BY clause to display the results in sorted
order. The extended syntax of SELECT statement is

SELECT column_list
FROM table_name
WHERE condition
ORDER BY column_name ASCENDING|DESCENDING

Q #50 ) Display the details of all students in class ‘C1’ in the order of their age
SELECT * FROM student WHERE class = ‘C1’ ORDER BY dob DESC
 PGDCA 106 Lab Practice - 2 (PC Skills & Database) Part III: SQL
Q #51 ) Display the gender, age and names of all students in class ‘C1’ in the order of gender and
descending order of age
SELECT gender, date_diff(curdate(), dob)/365.25 as age, name
FROM student
WHERE class = ‘C1’
ORDER BY gender, dob

Q #56 ) Display the class, number of students, maximum and minimum marks of all students
SELECT class, COUNT(name), MAX(mark), MIN(mark)
FROM student

Q #57) Display the salary, and number of employees drawing that salary
SELECT salary, COUNT(name)
FROM employee

9. SQL commands using Having clause

The SQL statement SELECT is used for retrieving data or selecting records from tables stored in an
SQL compliant RDBMS. The syntax with the optional WHERE clause to filter rows displayed in
the result, can be extended further to include an ORDER BY clause to display the results in sorted
order. The extended syntax of SELECT statement is

SELECT column_list
FROM table_name
WHERE condition
GROUP BY column_name
HAVING condition
ORDER BY column_name ASCENDING|DESCENDING

Q #58 ) Display the class and class average of those class class with class average >= 60
SELECT class, AVG(mark)
FROM student
GROUP BY class
HAVING AVG(class) >= 60

18. SQL commands using subqueries

A SELECT statement can be used in another SELECT statement, in the condition of WHERE
clause or HAVING clause or in the FROM clause.

Q #59 ) Display the name, gender and date of birth of the youngest student.
SELECT name, gender, dob
FROM student
WHERE dob = (SELECT MIN(dob) FROM student)

Q #60) Display the name, dob, doj and salary of all employees who draws the highest salary
SELECT name, dob, doj, salaray
FROM employee
WHERE salary = (SELECT MAX(salary) FROM employee)

Q #61 ) Display the maximum average mark of all the classes

SELECT MAX(avg_mark)
FROM (
 PGDCA 106 Lab Practice - 2 (PC Skills & Database) Part III: SQL
SELECT AVG(mark) avg_mark
FROM student
GROUP BY class
) tmp
Q #62 ) Display the class, number of students, class average, highest, and least of all students in the
class with the highest class average
SELECT class, COUNT(name), AVG(mark), MAX(mark), MIN(mark)
FROM student
GROUP BY class
HAVING AVG(mark) = (
SELECT MAX(avg_mark)
FROM
(SELECT AVG(mark) avg_mark
FROM student GROUP BY class
) tmp
)