在学习SQL语句,基本语法时,找些面试题学习稳固知识点,接接地气。下面的SQL面试题大概会有100题,分十季分享给大家,希望大家能坚持学下来,一定会有收获的!
第二季来了:
ORACLE和DB2支持的大对象是什么?MS SQL支持的大对象是什么?
在MS SQL中image数据类型的存储容量是多少?
在MS SQL中varbinary数据类型的存储容量是多少?
编写查询,按条件合并两列为一列(i.e.我想从FullName或FirstName的列中找出不为空的雇员的名字)。
如何使用查询创建标识列?
哪个函数可以用来比较两个不同的日期?
查找超过40岁的所有员工。
编写查询,从EMPLOYEE表中找到第二高薪水的员工。
编写查询,不使用JOIN操作符,从EMPLOYEE表中查找名字开头是“s”和居住在城市“Mumbai”的所有员工。
如何从EMPLOYEE表中删除重复的员工条目?
答案:
Oracle和DB2是BLOB(Binary Large Objects),CLOB(Character Large Objects)。MS SQL是image和varbinary。
最大为2G的可变长度的二进制数据。
最大为8000字节的可变长度的二进制数据。
SELECT Name = CASE
WHEN EMPLOYEE.FullName IS NOT NULL THEN EMPLOYEE.FullName
WHEN EMPLOYEE.FirstName IS NOT NULL THEN EMPLOYEE.FirstName
END
FROM EMPLOYEE
SELECT row_number() OVER(ORDER by TESTTABLE.p1) as SRNO,
result = CASE
WHEN TESTTABLE.p1 IS NOT NULL THEN TESTTABLE.p1
WHEN TESTTABLE.p2 IS NOT NULL THEN TESTTABLE.p2
END
FROM TESTTABLE
SELECT DATEDIFF(dd,'10‐10‐2017','15‐10‐2017');
SELECT COUNT(EMPLOYEES.*) FROM EMPLOYEES WHERE EMPLOYEES.Age>40
SELECT EMPLOYEE.*, MAX(EMPLOYEE.Salary) AS SalarySecondHighest
FROM EMPLOYEE
WHERE EMPLOYEE.Salary <
(SELECT MAX(EMPLOYEE.Salary) AS SalaryFirstHighest FROM EMPLOYEE)
SELECT EMPLOYEE.* FROM EMPLOYEE
WHERE CHARINDEX('s',EMPLOYEE.name) = 1 AND
CHARINDEX('Mumbai',EMPLOYEE.City) > 0
第一步,选择重复的员工记录并将其复制到holdTable表中:
SELECT EMPLOYEE.EmployeeID, COUNT(EMPLOYEE.EmployeeID), *
INTO HoldTable
FROM EMPLOYEE
GROUP BY EMPLOYEE.EmployeeID
HAVING COUNT(EMPLOYEE.EmployeeID) > 1
第二步,选择重复的行到另一个HoldDistinct表中:
SELECT DISTINCT EMPLOYEE.*
INTO HoldDistinct
FROM EMPLOYEE, HoldTable
WHERE EMPLOYEE.EmployeeID=HoldTable.EmployeeID
第三步,从EMPLOYEE表中删除重复行:
DELETE EMPLOYEE
FROM EMPLOYEE, HoldTable
WHERE EMPLOYEE.EmployeeID = HoldTable.EmployeeID
第四步,插于不同条目:
INSERT INTO EMPLOYEE(EmployeeID, FirstName, LastName, Salary, ...otherlist of columns)
SELECT EmployeeID, FirstName, LastName, Salary, ...other list of columns)
FROM HoldDistinct