SQL数据库软件设计面试题

1、Insert Into 数据表名称(字段名称1,字段名称2,.) values(字段值1,字段值2,.)insert into user(username,password,age) values(李老四,6666,45)Update 数据表名称 Set 字段名称=字段值,字段名称=字段值,.Where 条件Delete From 数据表下列查询返回在LONDON（伦敦）或SEATTLE（西雅图）的所有雇员：SELECT * FROM employees WHERE UPPER(city) IN (LONDON，SEATTLE)下面示例利用DATEDIFF函数，确定在 pubs 数据库中标题发布日期和当前日期间的天数。SELECT DATEDIFF(day, OrderDate, getdate() AS no_of_days FROM table1返回字符串wonderful在 titles 表的 notes 列中开始的位置。SELECT CHARINDEX(wonderful, notes)以下是返回的结果：（第47个字符位置）显示工作站的名称：select host_name()

2、 as Client Computer Name下例是检索 titles 表中百分之五十的书。如果 titles 表中包含了 18 行，则将检索前 9 行。SELECT TOP 50 PERCENT title FROM titles字段名称 Not Between 起始值 and 终止值列出BOOK表中30至50元的书select * from book where price between 30 and 50字段名称 Not In(列出值1,列出值2,.)从BOOK表中列出价格为30,40,50,60的所有书select * from book where price in(30,40,50,60)字段名称 Not Like 通配符列出BOOK表中出版社含电的所有记录select * from book where publishing like *电*列出BOOK表中出版社第一个字是电的所有记录select * from book where publishing like 电*-select Sum/Count/Avg/Max/Min(字段名称) As 新名称 From 数据

3、表名称sum求和：求出总价格做为合计字段select sum(price ) as 合计 from bookcount统计数量：统计BOOK表中有多少条记录做为数量字段select count(id) as 数量 from bookAVG平均：算出BOOK表中所有书的平均价格select avg(price) as 平均价格 from bookMAX最大：列出BOOK表中最贵的书select max(price) as 最贵书 from bookMIN最小：select min(price) as 最便宜书 from book交叉联接: SELECT * FROM table1 CROSS JOIN table2select x.name, y.name from x left join y on x.refid = y.idselect y.name, x.name from x right join y on x.refid = y.id表联接查询SELECT t1.ProductID, t2.VendorID, t2.NameFROM table1 t1, table2 t2WH

4、ERE t1.VendorID = t2.VendorID AND StandardPrice $10 AND Name LIKE F%SELECT ProductID, talbe2.VendorID, NameFROM table1 JOIN table2 ON (table1.VendorID = table2.VendorID)WHERE StandardPrice $10 AND Name LIKE F%COMPUTE by 子句可选 BY 关键字。它基于每一列计算指定的行聚合。行聚合函数名称。包括 SUM、AVG、MIN、MAX 或 COUNT。要对其执行行聚合函数的列。SELECT RateId, ConnectionFee, RateFROM UTC_RateORDER BY RateIdCOMPUTE SUM(ConnectionFee), SUM(Rate) -Compute用法（总计）SELECT RateId, ConnectionFee, RateFROM UTC_RateORDER BY RateIdCOMPUTE SUM(ConnectionFee), S

5、UM(Rate) BY RateId -Compute by用法（小计）DISTINCT 指定 AVG 只在每个值的唯一实例上执行，而不管该值出现了多少次。SELECT AVG(DISTINCT Rate)-计算不重复Rate的avgFROM UTC_RateSELECT distinct Rate FROM UTC_Rate表的复制Select intoSELECT * into newtable FROM oldtable联合查询 UNION所有查询中的列数和列的顺序必须相同, 数据类型必须兼容SELECT Country, FirstName, LastName FROM EmployeesUNION SELECT Country, NULL, NULL FROM Regions;子查询:-使用聚合函数SELECT title FROM titlesWHERE advance (SELECT max(advance) FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id WHERE pub_n

6、ame = New Moon Books)-使用allSELECT title FROM titlesWHERE advance all (SELECT advance FROM publishers INNER JOIN titles ON titles.pub_id = publishers.pub_id WHERE pub_name = New Moon Books)-嵌套SELECT fieldFROM table1WHERE id IN (SELECT id FROM table2 WHERE title_id IN (SELECT title_id FROM table3 WHERE title_id like PC%)-EXISTS 和NOT EXISTS 查找交集与差集SELECT DISTINCT field FROM table1WHERE EXISTS (SELECT * FROM table2 WHERE table1.field = table2.field)-等效SELECT DISTINCT table1.fieldFROM table1 INNER JO

7、IN table2ON table1.field = publishers.field-差集SELECT DISTINCT fieldFROM table1WHERE NOT EXISTS (SELECT * FROM table2 WHERE table1.field = table2.field)-等效SELECT DISTINCT fieldFROM table1WHERE field NOT IN (SELECT field FROM table2)指定组或聚合的搜索条件。HAVING 只能与 SELECT 语句一起使用。HAVING 通常在 GROUP BY 子句中使用。如果不使用 GROUP BY 子句，则 HAVING 的行为与 WHERE 子句一样。SELECT id, SUM(price) FROM SalesGROUP BY idHAVING SUM(price) 10.00-检索超过 $10.00 的每个 ID 的总计ORDER BY id ;更新表:UPDATE titlesSET price = price * 2WHERE pub_id IN (SELECT

8、 pub_id FROM publishers WHERE pub_name = New Moon Books)1：找出那些工资高于他们所在部门的manager的工资的员工。 select w.ename,w.sal,w.mgr,m.empno,m.ename,m.sal from emp w,emp m where w.mgr=m.empno and w.salm.sal2:哪些人是领导? select distinct m.ename from emp w,emp m where w.mgr=m.empno select ename from emp where empno in(select mgr from emp) select ename from emp o where exists (select a from emp i where o.empno=i.mgr)3：哪些人不是领导？ select m.ename from emp m,emp w where m.empno=w.mgr(+) and w.mgr is null select ename from emp where empno not in (select mgr from emp where mgr is not null) select ename from emp o where not exists (select a from emp i where o.empno=i.mgr) 哪个部门没有员工？哪个学生没有选课？哪个部门没有smith这个人？ select ename,emp.deptno,dept.deptno,dname from emp,dept where emp.deptno(+)=dept.deptno and ename(+)=SMITH and ename is null4：找出公司里收入最高的前三名员工： SQL select rownum, last_name, salary 2 from (select last_name, salary 3 f

《SQL数据库软件设计面试题》由会员晓***分享，可在线阅读，更多相关《SQL数据库软件设计面试题》请在金锄头文库上搜索。