Programs on SQL

By Aditya Kantiram Kumkar

Program 1 : Create a table Employee with constraints

  • Step-1 Create a database:
		CREATE DATABASE Bank;
  • Step-2 Display the databases 
		SHOW DATABASES;


  • Step-3: Enter into database.
		 Use Bank;


  • Step-4: Create the table Employee.
		CREATE TABLE Employee(
  				Ecode INT PRIMARY KEY,
 			    Ename VARCHAR(20) NOT NULL,
  				Dept VARCHAR(15),
 			    City VARCHAR(15),
  				Sex CHAR(1),
  				DOB DATE,
  				Salary DECIMAL(12,2)
		);
​


Program 2 : Insert data into the table

INSERT INTO Employee VALUES (1001, 'Atul', 'Production', 'Vadodara', 'M','1992-10-23', 23000.50);
​

Program 3 : Add a new column in a table.

ALTER TABLE Employee ADD address varchar(50);
​

Program 4 : Change the data-type and size of an existing column.

ALTER TABLE Employee MODIFY city char(30);

NOTE : No change in table records.

Program 5 : Write SQL queries using SELECT, FROM, WHERE clause based on EMPLOYEE table.

  • List the name of female employees in EMPLOYEE table. 
SELECT	Ename
FROM	Employee
WHERE	sex=’F’;
  • Display the name and department of those employees who work in Surat and salary is greater than 25000.
SELECT	Ename, Dept
FROM	Employee
WHERE	city=’surat’ and salary > 25000;
  • Display the name of those female employees who work in Mumbai. 
SELECT	Ename
FROM	Employee
WHERE	sex=’F’ and city=’Mumbai’;
  • Display the name of those employees whose department is marketing or RND.
SELECT	Ename
FROM	Employee
WHERE	Dept=’marketing’ OR Dept=’RND’;
  • List the name of employees who are not males.
SELECT	Ename, Sex
FROM	Employee
WHERE	sex!=’M’;

Program 6 : Display the name of departments. Each department should be displayed once.

SELECT DISTINCT(Dept) FROM	Employee;

Program 7: Find the name and salary of those employees whose salary is between 35000 and 40000.

SELECT Ename, salary FROM	Employee
WHERE salary BETWEEN 35000 and 40000;

Program 8 : Find the name of those employees who live in Guwahati, Surat or Jaipur city.

SELECT Ename, city FROM	Employee
WHERE city IN(‘Guwahati’,’Surat’,’Jaipur’);

Program 9 : Display the name of those employees whose name starts with ‘M’.

SELECT Ename FROM Employee
WHERE Ename LIKE ‘M%’;

Program 10 : List the name of employees not assigned to any department.

SELECT Ename FROM	Employee
WHERE Dept IS NULL;

Program 11 : Display the list of employees in descending order of employee code.

SELECT	* FROM	Employee ORDER BY Ecode DESC;

Program 12 : Find the average salary at each department.

SELECT Dept, avg(salary) FROM	Employee
group by	Dept;

Program 13 : Find maximum salary of each department and display the name of that department which has maximum salary more than 39000.

SELECT	Dept, max(salary) FROM	Employee
group by	Dept
HAVING	max(salary)>39000;

Program 14 :Queries for Aggregate functions- SUM( ), AVG( ), MIN( ), MAX( ), COUNT( )

  • Find the average salary of the employees in employee table.
SELECT	avg(salary) FROM	Employee;
  • Find the minimum salary of a female employee in EMPLOYEE table.
SELECT	Ename, min(salary) FROM Employee
WHERE	sex=’F’;
  • Find the maximum salary of a male employee in EMPLOYEE table.
SELECT	Ename, max(salary) FROM	Employee 
WHERE	sex=’M’;
  • Find the total salary of those employees who work in Guwahati city.
SELECT	sum(salary) FROM	Employee
WHERE	city=’Guwahati’;
  • Find the number of tuples (records) in the EMPLOYEE relation.
SELECT	count(*) FROM	Employee;


Popular posts from this blog