1.有以下表的记录
EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
Kochhar 5000 DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Sales
20 Marketing
30 Accounts
40 Administration 请你选择出所有员工的所在部门的名称(Last_Name,Department_ID,Department_Name)2.有这样一张表
EMPLOYEE_ID INT Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
以下那些语句是正确的( )A. INSERT INTO employees
VALUES ( NULL, ‘John’,‘Smith’)B. INSERT INTO employees( first_name, last_name)
VALUES(‘John’,‘Smith’) C. INSERT INTO employees
VALUES (‘1000’,‘John’,NULL) D. INSERT INTO employees(first_name,last_name, employee_id)
VALUES ( 1000, ‘John’,‘Smith’)E. INSERT INTO employees (employee_id)
VALUES (1000) F. INSERT INTO employees (employee_id, first_name, last_name)
VALUES ( 1000, ‘John’,‘’)
4.有一个角色,他具有表Employee的Select,Insert into,Modify 的权限,任何用户获得这一权限后,具有把这一权限赋予他人的能力。你应该怎样处理?
A. GRANT select, insert, update ON student_grades TO manager B. GRANT select, insert, update ON student_grades TO ROLE manager C. GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION D. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION E. GRANT select, insert, update ON student_grades TO ROLE manager WITH GRANT OPTION F.GRANT select, insert, modify ON student_grades TO ROLE manager WITH GRANT OPTION5.有这样一张表2
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
…
以下那些语句是正确的( )
A. SELECT *
FROM employees
where salary > (SELECT MIN(salary)
FROM employees GROUP BY department.id) B. SELECT *
FROM employees
WHERE salary = (SELECT AVG(salary)
FROM employees GROUP BY department_id) C. SELECT distinct department_id
FROM employees
Where salary > ANY (SELECT AVG(salary)
FROM employees GROUP BY department_id) D. SELECT department_id
FROM employees
WHERE SALARY > ALL (SELECT AVG(salary)
FROM employees GROUP BY department_id) E. SELECT last_name
FROM employees
Where salary > ANY (SELECT MAX(salary)
FROM employees GROUP BY department_id) F. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY))
6.有如下的表结构:
Badge varchar(10) Not Null
Name nvarchar(20) Not Null
DepID varchar(10) Null,
JobType varchar(10) Null,
Salary decimal(19,4) Null
请写出每个部门下,不同岗位类别下的最大,最小薪资,平均薪资7.在一表中具有一个日期型的字段(Term smalldatetime),以下那些分组函数是可以作用于这一字段 ( )
A.Sum(Term) B. Count(Term) C. Avg(Term) D. Max(Term) E. Min(Term) 8.有一表结构
Badge varchar(10) Not Null
Salary smallmoney
Pct decimal(9,2)
现求出每个员工的 调整后的薪资(12*salay*pct),如何书写这一语句9.有如下的标结构
ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
100 12-JAN-2000 15 10000
101 09-MAR-2000 40 8000
102 09-MAR-2000 35 12500
103 15-MAR-2000 15 12000
104 25-JUN-2000 15 6000
105 18-JUL-2000 20 5000
106 18-JUL-2000 35 7000
107 21-JUL-2000 20 6500
108 04-AUG-2000 10 8000 CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
40 Linda New York
请你查询出与Martin在同一天下订单的所有订单的情况
10.有一张Students的表,它现在是空的(没有记录),要求你对这张表的Student_ID列添加一个主键的约束。
11.有如下的语句
1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (SELECT dept_id, MAX(sal) maxsal
4 FROM employees
5 GROUP BY dept_id
6 Order by Sal ) b
7 WHERE a.dept_id = b.dept_id
8 AND a.sal < b.maxsal
请你指出,上句的错误处,并且说明错误的原因
12.有表 Employees 和Departments ,其结构参见 1题,Employees的Department_ID 是一个引用于Departments的外键,其定义的结构如下:
Alter Table Employees
Add Constraints FK_Employees_Department
Foreign Key (Department_id)
References Departments (Department_ID) On Update Cascade
如果执行如下的语句:Delete From Departments Where Department_ID = 10 会产生怎样的结果?
13.以下的值是多少:Round(Floor(1600%10),2)=14.有一学生的各科的成绩表 Marks
Std_ID int 学生的序号
Subj1 decimal(9,2) 课程1的成绩
Subj2 decimal(9,2) 课程2的成绩
SELECT subj1+subj2 total_s, std_id FROM s WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_s
以下哪个是正确的:( )
A. 能够正常地返回课程1和课程2都在各自平均分数线上的学生的课程成绩的总合
B. 语句会在Selec子句中,返回错误
C. 语句会在Where子句中,返回错误
D. 语句会在 Order by 子句中,返回错误15.有如下的表结构
Column name Data Type Res
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID
column of the DEPARTMENTS table
你要创建一张试图(eVEmployee),能够通过这一试图,新增记录,以下正确的是( )
A. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name,
department_id
FROM employees
WHERE mgr_id IN (102, 120) B. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, job_id
department_id
FROM employees
WHERE mgr_id IN (102, 120) C. CREATE VIEW emp_Vu AS
SELECT department_id, SUM(sal) TOTALSAL
FROM employees
WHERE mgr_id IN (102, 120)
GROUP BY department_id D. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, job_id,
DISTINCT department_id
FROM employees
16.有一字符窜,‘Hello Word’,请你实现如下的结果 ‘elloword’
17.有一表学生的成绩表,其结构如下:
Term smalldatetime not Null 学期
Std_ID int not null 学号
Subj_Id int not null 课程编号
Mark decimal(9,2) Null 课程成绩
Order samllint Null 次序
请你为每个学期每门课程排一个次序(按降序,成绩相同的同一次序),用Procedure和试图各自实现
EMPLOYEES
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
Kochhar 5000 DEPARTMENTS
DEPARTMENT_ID DEPARTMENT_NAME
10 Sales
20 Marketing
30 Accounts
40 Administration 请你选择出所有员工的所在部门的名称(Last_Name,Department_ID,Department_Name)2.有这样一张表
EMPLOYEE_ID INT Primary Key
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
以下那些语句是正确的( )A. INSERT INTO employees
VALUES ( NULL, ‘John’,‘Smith’)B. INSERT INTO employees( first_name, last_name)
VALUES(‘John’,‘Smith’) C. INSERT INTO employees
VALUES (‘1000’,‘John’,NULL) D. INSERT INTO employees(first_name,last_name, employee_id)
VALUES ( 1000, ‘John’,‘Smith’)E. INSERT INTO employees (employee_id)
VALUES (1000) F. INSERT INTO employees (employee_id, first_name, last_name)
VALUES ( 1000, ‘John’,‘’)
4.有一个角色,他具有表Employee的Select,Insert into,Modify 的权限,任何用户获得这一权限后,具有把这一权限赋予他人的能力。你应该怎样处理?
A. GRANT select, insert, update ON student_grades TO manager B. GRANT select, insert, update ON student_grades TO ROLE manager C. GRANT select, insert, modify ON student_grades TO manager WITH GRANT OPTION D. GRANT select, insert, update ON student_grades TO manager WITH GRANT OPTION E. GRANT select, insert, update ON student_grades TO ROLE manager WITH GRANT OPTION F.GRANT select, insert, modify ON student_grades TO ROLE manager WITH GRANT OPTION5.有这样一张表2
LAST_NAME DEPARTMENT_ID SALARY
Getz 10 3000
Davis 20 1500
King 20 2200
Davis 30 5000
…
以下那些语句是正确的( )
A. SELECT *
FROM employees
where salary > (SELECT MIN(salary)
FROM employees GROUP BY department.id) B. SELECT *
FROM employees
WHERE salary = (SELECT AVG(salary)
FROM employees GROUP BY department_id) C. SELECT distinct department_id
FROM employees
Where salary > ANY (SELECT AVG(salary)
FROM employees GROUP BY department_id) D. SELECT department_id
FROM employees
WHERE SALARY > ALL (SELECT AVG(salary)
FROM employees GROUP BY department_id) E. SELECT last_name
FROM employees
Where salary > ANY (SELECT MAX(salary)
FROM employees GROUP BY department_id) F. SELECT department_id FROM employees WHERE salary > ALL (SELECT AVG(salary) FROM employees GROUP BY AVG(SALARY))
6.有如下的表结构:
Badge varchar(10) Not Null
Name nvarchar(20) Not Null
DepID varchar(10) Null,
JobType varchar(10) Null,
Salary decimal(19,4) Null
请写出每个部门下,不同岗位类别下的最大,最小薪资,平均薪资7.在一表中具有一个日期型的字段(Term smalldatetime),以下那些分组函数是可以作用于这一字段 ( )
A.Sum(Term) B. Count(Term) C. Avg(Term) D. Max(Term) E. Min(Term) 8.有一表结构
Badge varchar(10) Not Null
Salary smallmoney
Pct decimal(9,2)
现求出每个员工的 调整后的薪资(12*salay*pct),如何书写这一语句9.有如下的标结构
ORDERS
ORD_ID ORD_DATE CUST_ID ORD_TOTAL
100 12-JAN-2000 15 10000
101 09-MAR-2000 40 8000
102 09-MAR-2000 35 12500
103 15-MAR-2000 15 12000
104 25-JUN-2000 15 6000
105 18-JUL-2000 20 5000
106 18-JUL-2000 35 7000
107 21-JUL-2000 20 6500
108 04-AUG-2000 10 8000 CUSTOMERS
CUST_ID CUST_NAME CITY
10 Smith Los Angeles
15 Bob San Francisco
20 Martin Chicago
25 Mary New York
30 Rina Chicago
35 Smith New York
40 Linda New York
请你查询出与Martin在同一天下订单的所有订单的情况
10.有一张Students的表,它现在是空的(没有记录),要求你对这张表的Student_ID列添加一个主键的约束。
11.有如下的语句
1 SELECT a.emp_name, a.sal, a.dept_id, b.maxsal
2 FROM employees a,
3 (SELECT dept_id, MAX(sal) maxsal
4 FROM employees
5 GROUP BY dept_id
6 Order by Sal ) b
7 WHERE a.dept_id = b.dept_id
8 AND a.sal < b.maxsal
请你指出,上句的错误处,并且说明错误的原因
12.有表 Employees 和Departments ,其结构参见 1题,Employees的Department_ID 是一个引用于Departments的外键,其定义的结构如下:
Alter Table Employees
Add Constraints FK_Employees_Department
Foreign Key (Department_id)
References Departments (Department_ID) On Update Cascade
如果执行如下的语句:Delete From Departments Where Department_ID = 10 会产生怎样的结果?
13.以下的值是多少:Round(Floor(1600%10),2)=14.有一学生的各科的成绩表 Marks
Std_ID int 学生的序号
Subj1 decimal(9,2) 课程1的成绩
Subj2 decimal(9,2) 课程2的成绩
SELECT subj1+subj2 total_s, std_id FROM s WHERE subj1 > AVG(subj1) AND subj2 > AVG(subj2) ORDER BY total_s
以下哪个是正确的:( )
A. 能够正常地返回课程1和课程2都在各自平均分数线上的学生的课程成绩的总合
B. 语句会在Selec子句中,返回错误
C. 语句会在Where子句中,返回错误
D. 语句会在 Order by 子句中,返回错误15.有如下的表结构
Column name Data Type Res
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
EMP_NAME VARCHAR2(30)
JOB_ID VARCHAR2(20) NOT NULL
SAL NUMBER
MGR_ID NUMBER References EMPLOYEE_ID column
DEPARTMENT_ID NUMBER Foreign key to DEPARTMENT_ID
column of the DEPARTMENTS table
你要创建一张试图(eVEmployee),能够通过这一试图,新增记录,以下正确的是( )
A. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name,
department_id
FROM employees
WHERE mgr_id IN (102, 120) B. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, job_id
department_id
FROM employees
WHERE mgr_id IN (102, 120) C. CREATE VIEW emp_Vu AS
SELECT department_id, SUM(sal) TOTALSAL
FROM employees
WHERE mgr_id IN (102, 120)
GROUP BY department_id D. CREATE VIEW emp_Vu AS
SELECT employee_id, emp_name, job_id,
DISTINCT department_id
FROM employees
16.有一字符窜,‘Hello Word’,请你实现如下的结果 ‘elloword’
17.有一表学生的成绩表,其结构如下:
Term smalldatetime not Null 学期
Std_ID int not null 学号
Subj_Id int not null 课程编号
Mark decimal(9,2) Null 课程成绩
Order samllint Null 次序
请你为每个学期每门课程排一个次序(按降序,成绩相同的同一次序),用Procedure和试图各自实现
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货