Ex.
No19
DATE:
SQL COMMANDS EXERCISE – 1
(Basic Queries – I)
AIM:
To write Queries for the following Questions based on the given table:
EmpID Name Gender Age Dept DOJ Salary City
1 Praveen M 25 Sales 1989-06-08 20000 Chennai
2 Arun M 29 Marketing 1989-09-26 22000 Chennai
3 Usha F 27 Finance 1994-08-09 25000 Bangalore
4 Bala M 31 Sales 1990-03-23 27000 NULL
5 Rani F 28 Marketing 1990-04-23 27000 Mumbai
6 Nisha F 26 NULL 1991-02-24 18000 Bangalore
7 Manoj M 32 Finance 1982-05-06 30000 Goa
(a) Write a Query to Create a new database in the name of "EMPS".
CREATE DATABASE EMPS;
(b) Write a Query to Open the database EMPS.
USE EMPS;
(c) Write a Query to create the above table called: Info
CREATE TABLE INFO (EmpID int primary key, Name varchar(15),
Gender varchar(3),Age int,Dept varchar(15),DOJ date, Salary int, City varchar(10));
(d) Write a Query to list all the existing database names.
SHOW DATABASES;
27
(e) Write a Query to List all the tables that exists in the current database.
SHOW TABLES;
Output:
(f) Write a Query to insert all the rows of above table into Info table.
INSERT INTO INFO VALUES (1,'Praveen','M', 25,'Sales','1989-06-08','20000','Chennai');
INSERT INTO INFO VALUES(2,'Arun','M',29,'Marketing','1989-09-26',22000,'Chennai');
INSERT INTO INFO VALUES(3,'Usha','F',27,'Finance','1994-08-09',25000,'Bangalore');
INSERT INTO INFO VALUES(4,'Bala','M',31,'Sales','1990-03-23',27000,NULL);
INSERT INTO INFO VALUES(5,'Rani','F',28,'Marketing','1990-04-23',27000,'Mumbai');
INSERT INTO INFO VALUES (6,'Nisha','F', 26, NULL,'1991-02-24', 18000,'Bangalore');
INSERT INTO INFO VALUES (7,'Manoj','M', 32,'Finance','1982-05-06', 30000,'Goa');
(g) Write a Query to display all the details of the Employees from the above table 'INFO'.
SELECT * FROM INFO;
Output:
****************************************************************************************************
28
Ex.No: 20
DATE:
SQL COMMANDS EXERCISE – 2
(Basic Queries – II)
AIM:
To write Queries for the following Questions based on the given table:
EmpID Name Gender Age Dept DOJ Salary City
1 Praveen M 25 Sales 1989-06-08 20000 Chennai
2 Arun M 29 Marketing 1989-09-26 22000 Chennai
3 Usha F 27 Finance 1994-08-09 25000 Bangalore
4 Bala M 31 Sales 1990-03-23 27000 NULL
5 Rani F 28 Marketing 1990-04-23 27000 Mumbai
6 Nisha F 26 NULL 1991-02-24 18000 Bangalore
7 Manoj M 32 Finance 1982-05-06 30000 Goa
(a) Write a Query to Display Employees’ name and City from the above table.
SELECT NAME, CITY FROM INFO;
Output:
(b) Write a Query to Display all details of Employees who are living in Chennai.
SELECT * FROM INFO WHERE CITY='CHENNAI';
Output:
29
(c) Write a Query to get the name and salary of the employee whose salary is above
15000 and gender is not male.
SELECT NAME,SALARY FROM INFO WHERE SALARY >15000 AND GENDER<>'M';
Output:
(d) Write a query to update increase 10% Salary of an employee whose City is 'CHENNAI'
and Gender is 'MALE'.
UPDATE INFO SET SALARY=SALARY+ (SALARY*0.10) WHERE
CITY='CHENNAI' AND GENDER='MALE';
Output (After Updating):
(e) Write a Query to delete the details of Employee Id 6.
DELETE FROM INFO WHERE EMPID=6;
Output (After Deletion):
*************************************************************************
30
Ex.No: 21
DATE:
SQL COMMANDS EXERCISE – 3
(Aggregate Functions, Order By Group By, Havning
Clause)
AIM:
To write Queries for the following Questions based on the given table:
EmpID Name Gender Age Dept DOJ Salary City
1 Praveen M 25 Sales 1989-06-08 20000 Chennai
2 Arun M 29 Marketing 1989-09-26 22000 Chennai
3 Usha F 27 Finance 1994-08-09 25000 Bangalore
4 Bala M 31 Sales 1990-03-23 27000 NULL
5 Rani F 28 Marketing 1990-04-23 27000 Mumbai
6 Nisha F 26 NULL 1991-02-24 18000 Bangalore
7 Manoj M 32 Finance 1982-05-06 30000 Goa
(a) Write a Query to list names of Employees in Descending order.
SELECT NAME FROM INFO ORDER BY NAME DESC;
Output:
(b) Write a Query to find a total salary of all employees.
SELECT SUM(SALARY) FROM INFO;
Output:
(c) Write a Query to display maximum salary and minimum salary of employees.
SELECT MAX(SALARY), MIN(SALARY) FROM INFO;
Output:
31
(d) Write a Query to count the number of employees earning more than 25000.
SELECT COUNT(SALARY) FROM INFO WHERE SALARY>25000;
Output:
(e) Write a query to display sum of salary of the employees grouped by department wise.
SELECT DEPT, SUM(SALARY) FROM INFO GROUP BY DEPT;
Output:
(f) Write a query to display the department names where number of employees are
greater than or equal to 2.
SELECT DEPT FROM INFO GROUP BY DEPT HAVING COUNT(*)>=2;
Output:
****************************************************************************************
32
Ex.No: 22
DATE:
SQL COMMANDS EXERCISE – 4
(Mathematical Functions)
AIM:
To write Queries for the following Questions based on the given table -"STU":
(a) Write a Query to Display square of age that got admission in the month of August.
SELECT POWER(AGE,2) FROM STU WHERE DOA LIKE '%-08-%';
Output:
(b) Write a Query to display Remainder of column Percentage divide by 3.
SELECT MOD(MARKS,3) FROM STU;
Output:
33
(c) Write a Query to display Student names and their Percentage in round figure.
SELECT NAME, ROUND(PERCENTAGE,0) FROM STU;
Output:
(d) Display Name, Percentage and round up the remainder marks up to 2 decimal
places.
SELECT NAME, ROUND(MOD(PERCENTAGE,3),2) FROM STU;
Output:
**************************************************************************************
34
Ex.No: 23
DATE:
SQL COMMANDS EXERCISE – 5
(Text Functions)
AIM:
To write Queries for the following Questions based on the given table -"STU":
(a) Write a Query to display Department name in lower case letters.
SELECT LCASE(DEPT) FROM STU;
Output:
(b) Write a Query to display department name and its respective number of characters
in Dept column.
SELECT DEPT,LENGTH(DEPT) FROM STU;
Output:
35
(c) Write a Query to display first 2 characters of the column Name.
SELECT LEFT(NAME,2) FROM STU;
Output:
(d) Write a Query to display first 2 characters of the column Name.
SELECT RIGHT(NAME,2) FROM STU;
Output:
(e) Write a query to display the names of all students and extract five characters from
the third position of the 'Name' field.
SELECT SUBSTR(NAME,3,5) FROM STU;
Output:
**********************************************************************************************
36
Ex.No: 24
DATE:
SQL COMMANDS EXERCISE – 6 (Date Functions)
AIM:
To write Queries for the following Questions based on the given table:
(a) Write a Query to display student name and month of date of admission of all
students.
SELECT NAME, MONTH(DOA) FROM STU;
Output:
(b) Write a Query to display Student name and day name of the students’ DOA of the
table STU.
SELECT NAME, DAYNAME(DOA) FROM STU;
Output:
37
(c) Write a query to display the joining year of IP students.
SELECT YEAR(DOA) FROM STU WHERE DEPT='IP'
Output:
(d) Write a Query to Display the month for the date_of_birth of all students.
SELECT NAME, MONTHNAME(DOA)FROM STU;
Output:
(e) Write a query to display the names of the students who joined in the month of June.
SELECT NAME FROM STU WHERE MONTHNAME(DOA)='June';
Output:
*********************************************************************************************
38
