SQL QUESTIONS WITH SYNTAX, QUERY AND OUTPUT
Employee Table
Q. Write a query to create a database named Company.
Syntax:
CREATE DATABASE database_name;
Query:
CREATE DATABASE Company;
Q. Write a query to use the Company database.
Syntax:
USE database_name;
Query:
USE Company;
Q. Write a query to create an Employee table.
Syntax:
CREATE TABLE table_name (column_name 1 datatype,
column_name 2 datatype,...);
Query:
CREATE TABLE Employee (EmpID INT PRIMARY KEY,EmpName
VARCHAR(50) NOT NULL,Department VARCHAR(30),Salary
INT,Gender VARCHAR(10),City VARCHAR(30), JoinDate DATE);
Q. Display structure of Employee table.
DESC Employee;
Q. Write a query to insert records into the Employee table.
Syntax:
INSERT INTO table_name VALUES (value1, value2,...);
Query:
INSERT INTO Employee VALUES
(101,'Isha','HR',35000,'Female','Delhi','2023-04-10'),
(102,'Priya','IT',42000,'Female','Mumbai','2022-06-15'),
(103,'Rahul','IT',50000,'Male','Delhi','2021-03-20'),
(104,'Neha','Sales',30000,'Female','Kolkata','2023-01-12'),
(105,'Amit','HR',38000,'Male','Chennai','2022-11-05');
Q. Write a query to display all records from the Employee table.
Syntax:
SELECT * FROM table_name;
Query:
SELECT * FROM Employee;
Q6. Write a query to display employee names and salaries only.
Syntax
SELECT column1, column2 FROM table_name;
Query
SELECT EmpName, Salary FROM Employee;
WHERE CLAUSE
Q. Write a query to display employees working in the IT
department.
Syntax
SELECT * FROM table_name WHERE condition;
Query
SELECT * FROM Employee WHERE Department = 'IT';
Q) DISPLAY empname and salary where Salary greater than
40000
SELECT empname,Salary FROM Employee WHERE Salary > 40000;
Q) Display female employees.
SELECT * FROM Employee WHERE gender = 'Female';
ORDER BY CLAUSE
Q) Write a query to display employees in descending order of
salary.
Syntax; SELECT * FROM table_name ORDER BY column_name
DESC;
Query
SELECT * FROM Employee ORDER BY Salary DESC;
UPDATE COMMAND
Q9. Write a query to update the salary of Rahul to 45000.
Syntax
UPDATE table_name SET column = value WHERE condition;
Query
UPDATE Employee SET Salary = 45000 WHERE EmpName ='Rahul';
b) Update city
UPDATE Employee SET City = 'Bangalore' WHERE EmpName =
'Rahul';
AGGREGATE FUNCTIONS
Q11. Write a query to count total number of employees.
Syntax
SELECT COUNT(column_name) FROM table_name;
Query
SELECT COUNT(EmpID) FROM Employee;
Output
COUNT(EmpID)
Q12. Write a query to find the total salary of all employees.
Syntax
SELECT SUM(column_name) FROM table_name;
Query
SELECT SUM(Salary) AS TotalSalary FROM Employee;
Output
TotalSalary
90000
b) AVG
SELECT AVG(Salary) AS AverageSalary FROM Employee;
c) MAX & MIN
SELECT MAX(Salary) AS HighestSalary FROM Employee;
SELECT MIN(Salary) AS LowestSalary FROM Employee;
LIKE OPEARTOR:
Names starting with ‘A’
SELECT * FROM Employee WHERE EmpName LIKE 'A%';
Names ending with ‘a’
SELECT * FROM Employee WHERE EmpName LIKE '%a';
Names having letter ‘ooj’
SELECT * FROM Employee WHERE EmpName LIKE '%ooj%';
BETWEEN OPERATOR
Write a query to display all records where salary should between
35000 and 45000.
SELECT * FROM Employee WHERE Salary BETWEEN 35000 AND
45000;
IN OPERATOR
Write a query to Return all employees from delhi and Mumbai.
SELECT * FROM Employee WHERE City IN ('Delhi', 'Mumbai');
Write a query to Return all employees OF THE DEPARTMENT HR & SALES
SELECT * FROM Employee WHERE department IN ('HR','Sales');
LOGICAL OPERATOR
AND / OR Operator
Display female employees from Delhi.
SELECT * FROM Employee WHERE Gender='Female' AND
City='Delhi';
DISPLAY THE RECORDS WHOSE CITY IS EITHER DELHI OR MUMBAI
SELECT * FROM Employee WHERE city='Delhi' OR city='Mumbai';
DELETE COMMAND
Write a query to delete the record of employee whose EmpID is
102.
Syntax
DELETE FROM table_name WHERE condition;
Query: DELETE FROM Employee WHERE EmpID = 102;
QUERY : Delete employees from Sales department.
DELETE FROM Employee WHERE department='Sales';
b) Delete all records
DELETE FROM Employee;
DROP TABLE
Write a query to drop the Employee table.
Syntax
DROP TABLE table_name;
Query
DROP TABLE Employee;