icc面试题目准备

9.write a query to create a table and make Customer ID as primary key ?

create table IF NOT EXISTS a ('customerid' int not null primary key);   #sql server版本

create table IF NOT EXISTS a ('customerid' int not null PRIMARY KEY (customerid)); #mysql版本

10.Write a SQL Query to find the 2nd or 3rd highest paid employee from an employee table(EMPID, name, salary, Deptname) from every dept using employee table (EMPID, name, salary, deptname).

SELECT 
(SELECT DISTINCT Salary FROM Employee 
 ORDER BY Salary DESC LIMIT 1 OFFSET 2) ;

11.Given a table TBL with a field number that has rows with the flowing values: 1,0,0,1,1,1,1,0,0,1,0,1,0,1,0,1. Write a query to add 2 where number is 0 and add 3 where number is 1.

select case
when '0' then 

12. How can you select all the even number records and all the odd number records form a table?

To select all the even number records from a table:

Select * from table where id % 2 = 0 

To select all the odd number records from a table:

Select * from table where id % 2 != 0

13.Assume that we have 2 tables(A and B). write a query to fetch values in table A that are and not in table B without using NOT keyword.

14.write a query to get gross sales of every product.

select (sale_price-unit_price) as gross from table group by product;

15.Table called orders, write query to find out the customers have not placed any order.

select customer where customer_id not in (select customer_id from order)；

16.write a query to find out customers and address, who have ordered IPHONE more than $800 from CA in the month April ?

select customer,address
from table
where product='iphone'
and state = 'ca'
and month ='april';

17.write a query to display the names of customers who have ordered more than 2 times?

select customer
from table
group by customer
having count(order_id)>2;

18.What will be the logic to remove duplicate records from table?

参考
https://www.mssqltips.com/sqlservertip/1918/different-strategies-for-removing-duplicate-records-in-sql-server/

19.check the table structure DEPT(DEPTID, DNAME, LOCATTION), EMP(EID, ENAME, SALARY, DEPTID). write a query to get department details and salary of all the employees.

select *, e.salary
from dept d join emp e on d.deptid=e.deptid;