SQL Q and A: TOP 40 Q and A
Hope this Help for You All !!
******************************************************************************
To select specific rows with specific columns:
1.select epmloyee_name,employee_id where employee id=45;
******************************************************************************
comparison operator:
2.SELECT * from products where price < 18
Between Operator:
SELECT * from products where price between 10 and 70;
******************************************************************************
3.SELECT DISTINCT(Salary) FROM table ORDER BY Salary DESC
Select TOP 1 price from (SELECT DISTINCT TOP 3 Price from Products ORDER BY price ASC) ORDER BY price DESC
SELECT MAX(price) FROM products WHERE price NOT IN (SELECT MAX(price) FROM products)
SELECT distinct price FROM [Products] order by price desc
SELECT DISTINCT price FROM Products ORDER BY Price DESC LIMIT 0,3
************************************************************************************************
4. Write a query min and max values in table?
select min( ) from employees
select Max() from employees
select sum() from employees
select AVG() from employees
5.How to add a new column name in existing table?
Alter table employees add salary int;
6.How to delete a column name in existing table?
ALTER TABLE employees DROP COLUMN column_name
;
7.How to delete the few rows or more than rows in existing table? For example 7,8,9,10,11 Records at a time
Delete from employees where employeesID In(7,8,9,10,11)
8.How to delete the Single rows in table?
Delete from employees where employeesID = 5
9.How to update the records in table?
Update employees set name = " Aguja ", salary = 34939 from employees where id = 10 ;
update Employees set salary=4000 where EmployeeID IN(2,3,6)
10.Group by sql;
Select country, count(*) from suppliers group by country
Select SupplierName,address,city,country from employees group by country;
Select ProductName,Unit from Products group by price;
Select ProductName,count(price),price,Unit from Products group by price;
----------------------------------------------------------------------
11.AVG()
Select AVG(price) from products;
SELECT ProductID,product name from products where Price> (select AVG(price) from products)
12.Count()
select count(price) from employees
select count(*) from employees
select count(distinct salary) from employees;
13.First() or TOP or Limit
-------------------------------------------------------------------------
Fiding who are getting TOP salary in table
select employeeID,lastname,salary from employees order by salary desc limit 1
14.LOWER(), UPPER()
select lower(lastname) from employees oder by eid,name
select Upper(lastname) from employees oder by eid,name
15.Round()
select employeeid,lastname, round(salary)as salary from employees
16.Delete()
Delete from shippers
Drop table shippers
delete from suppliers where supplierID=2
delete form supplierName where city= ' France '
17.Date and time functions:
---------------------------------
select date()
select time()
18.And Or operator
-----------------------
SELECT * FROM [Orders] where orderdate='1996-07-04' or ShipperID=2
SELECT * FROM [Orders] where orderdate='1996-07-04' and ShipperID=2
19.NULL and NOT NULL
----------------------------
SELECT * FROM [Employees] where salary is NOT NULL
select * from employees where salary is null
************************************************
20.INDEX :
create index index_name on employees(column1,column2,column3)
Drop index : Drop index employee.index1 -> MS Sql
Drop index : Alter table employee drop index index1
Drop Table EMployee
Drop Database database_name
DROP INDEX table_name.index_name
ALTER TABLE table_name DROP INDEX index_name
Truncate table Employee
Index allow to find data fast,. without reading the whole table.
TO find data more quicly and efficient.
21.Default
----------------------------------------------------------------------------------------------
create table bottle(id int,name varchar(15),city char(7) default 'chennai')
22. Subquery A query in a query
select * from customers where customerID IN(select customerID from customers where city='London')
select * from customers where CustomerID NOT IN (select customerID from customers where city='London')
select * from employees emp, orders ord where emp.salary >15000 and emp.employeeID=ord.EmployeeID - Simple
insert into employees values add column (salary) alter table employees alter column salary
------------------------------------------------------------------------------------------------
23.
create table Location(Location_ID int(8),Regional_Group char(10))
Insert into Department(Department_ID,Name,Location_ID)values(20,'Operations',167)
create table Employees(Employee_ID int(8),Last_Name Char(12),First_Name Char(12),Job_ID int(8),HIREDATE,Salary int(8),Department_ID int(8),Date Date)
Insert into Employees(Employee_Id,Last_Name,First_Name,Job_ID,HIREDATE,Salary,Department_ID,Date Date)
values(7369,'SMITH','JOHN',667,'17-DEC-1984','2001-06-06',9819,20)
Insert into Employee(Employee_Id,Last_Name,First_Name,Job_ID,HIREDATE,Salary,Department_ID,Date)
values(7505,'DOYLE','JEAN',671,'2007-2-22',45000,30,'2007-09-14')
24.select Min(salary),Max(Salary),Avg(salary) from Employee group by job
-----------------------------------------------------------
25.Select Employee,Max(Salary) from employee
26.Display the employee who are working on sales department?
Select Employee_ID,Last_Name,First_Name from employee where job= 'Sales'
27.Display the employees who are working as "Clerk".
28.Display the employees who are working in "New York"
29.Find out no.of employees working in "Sales" department.
Select Count(*) from employee where job='Sales'
30.Update the employees salaries, who are working as Clerk on the basis of 10%.
31.Delete the employees who are working in jov QA
Delete from employee where job ='QA'
32.Display the second highest salary drawing employee details.
33.Display the Nth highest salary drawing employee details
----------------------------------------------------------------------------------------------
SubQuery: A Query inner query is execute then outer Query,.
A.who are getting salary higher than Borker?
Select * from employee where salary >(select salary from employee where last_name='Borker')
B.Finding the full_name of the Db(QA) department employees whose salary is greater than their average department salary?
Select Employee_ID,Job from employee where salary>(select AVG(salary) from employee where Job='QA')
C.Finding the salary between the 2 employees (kumar,marimuthu)
Select * from employee where salary between(Select salary from employee where Last_name='Kumar') and (Select salary from employee where Last_name='Maari')
D. whose avg(salary) is greater than QA is AVG(Salary) ?
< Select salary,AVG(Salary) from employee where job='QA'
E. Department wise Avg salary?
select job,Avg(salary) from employee group by job having AVG(salary)
--------------------------------------------------------------------------------------------
34.Write syntax for views and stored procedures?
View Syntax:
Create view <view-name> as select <column names> from <table-name> where <condition> - create view v1 as select c1,c2 from Employee;
35.Stored Procedure Sysntax:
Create procedure <procedure-name> as begin select <column names> from <table-name> end
Create. procedure palm as
begin select c1 from employee
end
Note: These are only sample examples, we can write in different ways also.
36.What are the different constraints available in sql and Explain
Not Null -> Enforces a column Not accept Null values- Column no need to accept null values
Unique -> uniquely identifies each record in a database table
Primary Key -> uniquely identifies each record in a database table and it cannot contain NULL values.
Foreign Key -> A Foreign Key in one table points to a PRIMARY KEY in another table.
Check -> It is used to limit the value range that can be placed in a column.
Default -> It is used to insert a default value into a column.
37.Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
38.
update employee set id=345 where name='milika'
Uniquely identifies each records in a database table
uniquely identifies each records in a table and can't contain null values
39.What is the operator in sql to find pattern match
Like
Select c1 from employee where c1 like 'v%'
Ex:
a.
Select ename from emp where ename like ‘v%’
It will display all the names which are start with ‘v’
b.
Select ename from emp where ename not like ‘v%’
It will display other than all the names which are start with ‘v’
40. What is the purpose of ‘distinct’ in sql
Distinct is used to display only the different values {Disticnt get the diffetent values from records
Ex:
Select distinct <col-names> from <table-name>
Select distinct ename from employee..
I will Share you soon,.. Next post soon..
Hope this Help for You All !!
******************************************************************************
To select specific rows with specific columns:
1.select epmloyee_name,employee_id where employee id=45;
******************************************************************************
comparison operator:
2.SELECT * from products where price < 18
Between Operator:
SELECT * from products where price between 10 and 70;
******************************************************************************
3.SELECT DISTINCT(Salary) FROM table ORDER BY Salary DESC
Select TOP 1 price from (SELECT DISTINCT TOP 3 Price from Products ORDER BY price ASC) ORDER BY price DESC
SELECT MAX(price) FROM products WHERE price NOT IN (SELECT MAX(price) FROM products)
SELECT distinct price FROM [Products] order by price desc
SELECT DISTINCT price FROM Products ORDER BY Price DESC LIMIT 0,3
************************************************************************************************
4. Write a query min and max values in table?
select min( ) from employees
select Max() from employees
select sum() from employees
select AVG() from employees
5.How to add a new column name in existing table?
Alter table employees add salary int;
6.How to delete a column name in existing table?
ALTER TABLE employees DROP COLUMN column_name
;
7.How to delete the few rows or more than rows in existing table? For example 7,8,9,10,11 Records at a time
Delete from employees where employeesID In(7,8,9,10,11)
8.How to delete the Single rows in table?
Delete from employees where employeesID = 5
9.How to update the records in table?
Update employees set name = " Aguja ", salary = 34939 from employees where id = 10 ;
update Employees set salary=4000 where EmployeeID IN(2,3,6)
10.Group by sql;
Select country, count(*) from suppliers group by country
Select SupplierName,address,city,country from employees group by country;
Select ProductName,Unit from Products group by price;
Select ProductName,count(price),price,Unit from Products group by price;
----------------------------------------------------------------------
11.AVG()
Select AVG(price) from products;
SELECT ProductID,product name from products where Price> (select AVG(price) from products)
12.Count()
select count(price) from employees
select count(*) from employees
select count(distinct salary) from employees;
13.First() or TOP or Limit
-------------------------------------------------------------------------
Fiding who are getting TOP salary in table
select employeeID,lastname,salary from employees order by salary desc limit 1
14.LOWER(), UPPER()
select lower(lastname) from employees oder by eid,name
select Upper(lastname) from employees oder by eid,name
15.Round()
select employeeid,lastname, round(salary)as salary from employees
16.Delete()
Delete from shippers
Drop table shippers
delete from suppliers where supplierID=2
delete form supplierName where city= ' France '
17.Date and time functions:
---------------------------------
select date()
select time()
18.And Or operator
-----------------------
SELECT * FROM [Orders] where orderdate='1996-07-04' or ShipperID=2
SELECT * FROM [Orders] where orderdate='1996-07-04' and ShipperID=2
19.NULL and NOT NULL
----------------------------
SELECT * FROM [Employees] where salary is NOT NULL
select * from employees where salary is null
************************************************
20.INDEX :
create index index_name on employees(column1,column2,column3)
Drop index : Drop index employee.index1 -> MS Sql
Drop index : Alter table employee drop index index1
Drop Table EMployee
Drop Database database_name
DROP INDEX table_name.index_name
ALTER TABLE table_name DROP INDEX index_name
Truncate table Employee
Index allow to find data fast,. without reading the whole table.
TO find data more quicly and efficient.
21.Default
----------------------------------------------------------------------------------------------
create table bottle(id int,name varchar(15),city char(7) default 'chennai')
22. Subquery A query in a query
select * from customers where customerID IN(select customerID from customers where city='London')
select * from customers where CustomerID NOT IN (select customerID from customers where city='London')
select * from employees emp, orders ord where emp.salary >15000 and emp.employeeID=ord.EmployeeID - Simple
insert into employees values add column (salary) alter table employees alter column salary
------------------------------------------------------------------------------------------------
23.
create table Location(Location_ID int(8),Regional_Group char(10))
Insert into Department(Department_ID,Name,Location_ID)values(20,'Operations',167)
create table Employees(Employee_ID int(8),Last_Name Char(12),First_Name Char(12),Job_ID int(8),HIREDATE,Salary int(8),Department_ID int(8),Date Date)
Insert into Employees(Employee_Id,Last_Name,First_Name,Job_ID,HIREDATE,Salary,Department_ID,Date Date)
values(7369,'SMITH','JOHN',667,'17-DEC-1984','2001-06-06',9819,20)
Insert into Employee(Employee_Id,Last_Name,First_Name,Job_ID,HIREDATE,Salary,Department_ID,Date)
values(7505,'DOYLE','JEAN',671,'2007-2-22',45000,30,'2007-09-14')
24.select Min(salary),Max(Salary),Avg(salary) from Employee group by job
-----------------------------------------------------------
25.Select Employee,Max(Salary) from employee
26.Display the employee who are working on sales department?
Select Employee_ID,Last_Name,First_Name from employee where job= 'Sales'
27.Display the employees who are working as "Clerk".
28.Display the employees who are working in "New York"
29.Find out no.of employees working in "Sales" department.
Select Count(*) from employee where job='Sales'
30.Update the employees salaries, who are working as Clerk on the basis of 10%.
31.Delete the employees who are working in jov QA
Delete from employee where job ='QA'
32.Display the second highest salary drawing employee details.
33.Display the Nth highest salary drawing employee details
----------------------------------------------------------------------------------------------
SubQuery: A Query inner query is execute then outer Query,.
A.who are getting salary higher than Borker?
Select * from employee where salary >(select salary from employee where last_name='Borker')
B.Finding the full_name of the Db(QA) department employees whose salary is greater than their average department salary?
Select Employee_ID,Job from employee where salary>(select AVG(salary) from employee where Job='QA')
C.Finding the salary between the 2 employees (kumar,marimuthu)
Select * from employee where salary between(Select salary from employee where Last_name='Kumar') and (Select salary from employee where Last_name='Maari')
D. whose avg(salary) is greater than QA is AVG(Salary) ?
< Select salary,AVG(Salary) from employee where job='QA'
E. Department wise Avg salary?
select job,Avg(salary) from employee group by job having AVG(salary)
--------------------------------------------------------------------------------------------
34.Write syntax for views and stored procedures?
View Syntax:
Create view <view-name> as select <column names> from <table-name> where <condition> - create view v1 as select c1,c2 from Employee;
35.Stored Procedure Sysntax:
Create procedure <procedure-name> as begin select <column names> from <table-name> end
Create. procedure palm as
begin select c1 from employee
end
Note: These are only sample examples, we can write in different ways also.
36.What are the different constraints available in sql and Explain
Not Null -> Enforces a column Not accept Null values- Column no need to accept null values
Unique -> uniquely identifies each record in a database table
Primary Key -> uniquely identifies each record in a database table and it cannot contain NULL values.
Foreign Key -> A Foreign Key in one table points to a PRIMARY KEY in another table.
Check -> It is used to limit the value range that can be placed in a column.
Default -> It is used to insert a default value into a column.
37.Here are the different types of the JOINs in SQL:
(INNER) JOIN: Returns records that have matching values in both tables
LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table
RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table
FULL (OUTER) JOIN: Return all records when there is a match in either left or right table
38.
update employee set id=345 where name='milika'
Uniquely identifies each records in a database table
uniquely identifies each records in a table and can't contain null values
39.What is the operator in sql to find pattern match
Like
Select c1 from employee where c1 like 'v%'
Ex:
a.
Select ename from emp where ename like ‘v%’
It will display all the names which are start with ‘v’
b.
Select ename from emp where ename not like ‘v%’
It will display other than all the names which are start with ‘v’
40. What is the purpose of ‘distinct’ in sql
Distinct is used to display only the different values {Disticnt get the diffetent values from records
Ex:
Select distinct <col-names> from <table-name>
Select distinct ename from employee..
I will Share you soon,.. Next post soon..
