sql test 1

SQL SERVER 2000 / SQL

1 ) To restrict a group query the clause used:
a)having
b)where
c)union
d)intersect

2) An example of Data Definition language is
a)Create
b)select
c)Insert
d)delete

3)An example of Data Manipulation is
a)create
b) Alter
c) Update
d) Drop

4)Delete command
a)data cannot be recovered
b)data can be restored
c)data and table are removed with this command
d)none of the above

5)Display all employs whose names end with h
a)Select * from employ where ename is %h;
b) Select * from employ where ename like `%h`
c)Select * from employ where ename=`*h`
d) Select * from employ where length(ename -1)=`h`

6)Display all the employnames in ascending order
a)Select * from employ order by ename
b) Select * from employ sort by ename ascending
c)Select * from employ group by ename order by ename ascending
d)none of the above

7)Dropped table can be retrieved by
a)Rollback
b)Save point to s1 then rollback to s1
c) Revoke table
d) None of the above

8)How do you copy a table employ with data into a new table back_emp with data
a)Create table back_emp as select * from employ;
b)Create table back_emp as insert from employ;
c)Create table back_emp; then export from employ and then import to back_emp;
d)Tables cannot be copied

9)How do you add data into Employee table

Attributes are empno,ename,dob,job,deptno,sal
a)insert into Employ values (1, ‘user1’,`10-jan-2007`,’Manager’,10, 24000.00)
b)append into Employ values (1, ‘user1’,`10-jan-2007`,’Manager’,10, 24000.00)
c)insert into Employ value (1, ‘user1’,`10-jan-2007`,’Manager’,10, 24000.00)
d)update Employ set value (1, ‘user1’,`10-jan-2007`,’Manager’,10, 24000.00)

10)Not Null does allow
a)unique values

b)null values
c)Repetitive values and not null values
d) None of the above

11)Select empno, sum(sal), deptno from employ group by deptno
a)we get proper output
b)No, we donot get proper output
c)The output is multiplied
d)No output ,an error is displayed.

12)To retrieve data in name column ename with second character as “a”
a)Select * from employ where ename like `?a%`
b)Select * from employ where ename like `_a%`
c)Select * from employ where ename like `2a%`
d)None of the above

13)Unique Key does allow
a)unique values and null values
b)Repetitive values
c)None of the above
d)not null values

14) Examine the description of the STUDENTS table:

STD_ID NUMBER (4)

COURSE_ID VARCHAR2 (10)

START_DATE DATE

END_DATE DATE

Which aggregate function is valid on the START_DATE column?

A. SUM(start_date)

B. AVG(start_date)

C. COUNT(start_date)

D. AVG(start_date, end_date)

15) Examine the description of the EMPLOYEES table:

EMP_ID NUMBER (4) NOT NULL

LAST_NAME VARCHAR2 (30) NOT NULL

FIRST_NAME VARCHAR2 (30)

DEPT_ID NUMBER (2)

JOB_CAT VARCHAR (30)

SALARY NUMBER (8, 2)

Which statement shows the department ID, minimum salary, and maximum salary paid in that department, only if the minimum salary is less than 5000 and maximum salary is more than 15000?

A. SELECT dept_id, MIN (salary), MAX (salary) FROM employees WHERE MIN(salary) <> 15000;

B. SELECT dept_id, MIN (salary), MAX (salary) FROM employees WHERE MIN (salary) <>

C. SELECT dept_id, MIN(salary), MAX(salary) FROM employees HAVING MIN (salary) <>

D. SELECT dept_id, MIN (salary), MAX (salary) FROM employees GROUP BY dept_id HAVING MIN(salary) <> 15000

16 ) Which is valid Oracle constraint types?

A. CASCADE

B. NONUNIQUE

C. CHECK

D CONSTANT

17. Which SQL statement would you use to remove a view called EMP_DEPT_VU from your schema?

A. DROP emp_dept_vu;

B. DELETE emp_dept_vu;

C. REMOVE emp_dept_vu;

D. DROP VIEW emp_dept_vu;

18. Which is an SQL*Plus command?

A. INSERT

B. UPDATE

C. SELECT

D. DESCRIBE

19) Evaluate this SQL statement:

SELECT ename, sal, 12*sal+100 FROM EMP;

The SAL column stores the monthly salary of the employee. Which change must be made to the above syntax to calculate the annual compensation as "monthly salary plus a monthly bonus of $100, multiplied by 12"?

a)No change is required to achieve the desired results.

b)SELECT ename, sal, 12*(sal+100) FROM emp;

c)SELECT ename, sal, (12*sal)+100 FROM emp;

d)SELECT ename, sal+100,*12 FROM emp;

20)The STUDENT_GRADES table has these columns:

STUDENT_ID NUMBER (12)

SEMESTER_END DATE

GPA NUMBER (4, 3)

The registrar requested a report listing the students' grade point averages (GPA) sorted from highest grade point average to lowest. Which statement produces a report that displays the student ID and GPA in the sorted order requested by the registrar?

A)SELECT student_id, gpa FROM student_grades ORDER BY gpa ASC;

B)SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa ASC;

C)SELECT student_id, gpa FROM student_grades SORT ORDER BY gpa;

D)SELECT student_id, gpa FROM student_grades ORDER BY gpa DESC;

21)Which statement creates a new user?

A)CREATE NEW USER Susan DEFAULT;

B)CREATE USER Susan IDENTIFIED BY blue;

C)CREATE NEW USER Susan IDENTIFIED by blue;

D)CREATE OR REPLACE USER Susan IDENTIFIED BY blue;

22) Examine the structure of the EMPLOYEES table:

EMPLOYEE_ID NUMBER Primary Key

FIRST_NAME VARCHAR2 (25)

LAST_NAME VARCHAR2 (25)

HIRE_DATE DATE

Which UPDATE statement is valid?

A)UPDATE employees SET first_name = 'John' SET last_name='Smith' WHERE employee_id = 180;

B)UPDATE employees SET first_name = 'John', SET last_name ='Smith' WHERE employee_id = 180;

C)UPDATE employees SET first_name = 'John' AND last_name ='Smith' WHERE employee_id = 180;

D)UPDATE employees SET first_name = 'John', last_name ='Smith' WHERE employee_id = 180;

23)Examine the structure of the EMPLOYEES table:

Column name Data type Remarks

EMPLOYEE_ID NUMBER NOT NULL, Primary Key

LAST_NAME VARCHAR2 (30)

FIRST_NAME VARCHAR2 (30)

JOB_ID NUMBER

SAL NUMBER

MGR_ID NUMBER

References EMPLOYEE_ID column DEPARTMENT_ID NUMBER

You need to create an index called NAME_IDX on the first name and last name fields of the EMPLOYEES table. Which SQL statement would you use to perform this task?

A)CREATE INDEX NAME_IDX ON employees(first_name, last_name);

B)CREATE INDEX NAME_IDX (first_name, last_name);

C)CREATE INDEX NAME_IDX (first_name AND last_name);

D)CREATE INDEX NAME_IDX ON employees (first_name AND last_name);

24) The CUSTOMERS table has these columns:

CUSTOMER_ID NUMBER (4) NOT NULL

CUSTOMER_NAME VARCHAR2 (100) NOT NULL

CUSTOMER_ADDRESS VARCHAR2 (150)

CUSTOMER_PHONE VARCHAR2 (20)

You need to produce output that states "Dear Customer customer_name”. The customer_name data values come from the CUSTOMER_NAME column in the CUSTOMERS table. Which statement produces this output?

A. SELECT dear customer, customer_name, FROM customers;

B. SELECT "Dear Customer", customer_name || ',' FROM customers;

C. SELECT 'Dear Customer ' || customer_name ',' FROM customers;

D. SELECT 'Dear Customer ' || customer_name || ',' FROM customers;

25. What is true of using group functions on columns that contain NULL values?

A)Group functions on columns ignore NULL values.

B)Group functions on columns returning dates include NULL values.

C)Group functions on columns returning numbers include NULL values.

D)Group functions on columns cannot be accurately used on columns that contain NULL values.

26) Mark for review a data manipulation language statement _____.

A)completes a transaction on a table

B)modifies the structure and data in a table

C)modifies the data but not the structure of a table

D)modifies the structure but not the data of a table

27 Mark for review you need to produce a report for mailing labels for all customers. The mailing label must have only the customer name and address. The CUSTOMERS table has these columns:

CUST_ID NUMBER (4) NOT NULL

CUST_NAME VARCHAR2 (100) NOT NULL

CUST_ADDRESS VARCHAR2 (150)

CUST_PHONE VARCHAR2 (20)

Which SELECT statement accomplishes this task?

A)SELECT *FROM customers;

B)SELECT name, address FROM customers;

C)SELECT id, name, address, phone FROM customers;

D)SELECT cust_name, cust_address FROM customers;

28 Mark for review The PRODUCTS table has these columns:

PRODUCT_ID NUMBER (4)

PRODUCT_NAME VARCHAR2 (45)

PRICE NUMBER (8, 2)

Evaluate this SQL statement:

SELECT * FROM PRODUCTS ORDER BY price, product_name;

What is true about the SQL statement?

A)The results are not sorted.

B)The results are sorted numerically.

C)The results are sorted numerically and then alphabetically.

D)The results are sorted alphabetically.

29 Mark for review. In which two cases would you use an outer join?

A)The tables being joined have only unmatched data.

B)The tables being joined have both matched and unmatched data.

C)The tables being joined have NOT NULL columns.

D)The tables being joined have only matched data.

30) Mark for review. Which constraint can be defined only at the column level?

A. UNIQUE

B. NOT NULL

C. CHECK

D. PRIMARY KEY

31) Mark for review. Evaluate the SQL statement:

TRUNCATE TABLE DEPT;

Which satement is true ?

A)It does not release the storage space used by the table.

B)You can roll back the deletion of rows after the statement executes.

C)You can NOT rollback the deletion of rows after the statement executes.

D)An attempt to use DESCRIBE on the DEPT table after the TRUNCATE statement executes will display an error.

32)Mark for review A SELECT statement can be used to perform these three functions: - Choose rows from a table. - Choose columns from a table. - Bring together data that is stored in different tables by creating a link between them. Which set of keywords describes these capabilities?

A)difference, projection, join

B)selection, projection, join

C)selection, intersection, join

D)intersection, projection, join

33) From SQL*Plus, you issue this SELECT statement:

SELECT * FROM orders;

You use this statement to retrieve data from a database table for _______________.

A. updating

B. viewing

C. deleting

D. truncating

34)Which two statements is true regarding the ORDER BY clause?

A)The sort is in descending order by default

B)The ORDER BY clause must precede the WHERE clause.

C)The ORDER BY clause is executed on the client side

D)The ORDER BY clause comes last in the SELECT statement

35) Evaluate the set of SQL statements:

CREATE TABLE dept (dept_id NUMBER (2) dname VARCHAR2 (14), Loc VARCHAR2 (13)); ROLLBACK;

DESCRIBE DEPT

What is true about the set?

A)The DESCRIBE DEPT statement displays the structure of the DEPT table

B)The ROLLBACK statement frees the storage space occupied by the DEPT table.

C)The DESCRIBE DEPT statement returns an error ORA-04043: object DEPT does not exist

D)The DESCRIBE DEPT statement displays the structure of the DEPT table only if there is a COMMIT statement introduced before the ROLLBACK statement.

36. Which clause would you use in a SELECT statement to limit the display to those employees whose salary is greater than 5000?

a)ORDER BY SALARY > 5000

b)GROUP BY SALARY > 5000

c)HAVING SALARY > 5000

d)WHERE SALARY > 5000

37)Which statement about sequences are true?

a)You use a NEXTVAL pseudo column to look at the next possible value that would be generated from a sequence, without actually retrieving the value.

b) use a NEXTVAL pseudo column to obtain the next possible value from a sequence by actually retrieving the value form the sequence

c)You use a CURRVAL pseudo column to generate a value from a sequence that would be used for a specified database column.

d)If a sequence starting from a value 100 and incremented by 1 is used by more than one application, then all of these applications could have a value of 105 assigned to their column whose value is being generated by the sequence.

38)Which statement about creating constraints are true?

A. Constraint names must start with SYS_C.

B. All constraints must be defined at the column level

C. Constraints can be created after the table is created

D. Information about constraints is found in the VIEW_CONSTRAINTS dictionary view

39) You are granted the CREATE VIEW privilege. What does this allow you to do?

A. create a table view

B. create a view in any scheme

C. create a view in your schema

D. create a sequence view in any schema

40)You need to calculate the total of all salaries in the accounting department. Which group function should you use?

A. MAX

B. MIN

C. SUM

D. COUNT

41) The CUSTOMERS table has these columns:

CUSTOMER_ID NUMBER (4) NOT NULL

CUSTOMER_NAME VARCHAR2 (100)

STREET_ADDRESS VARCHAR2 (150)

CITY_ADDRESS VARCHAR2 (50)

STATE_ADDRESS VARCHAR2 (50)

PROVINCE_ADDRESS VARCHAR2 (50)

COUNTRY_ADDRESS VARCHAR2 (50)

POSTAL_CODE VARCHAR2 (12)

CUSTOEMR_PHONE VARCHAR2 (20)

Which statement finds the rows in the CUSTOMERS table that do not have a postal code

A. SELECT customer_id, customer_name FROM customers WHERE postal_code CONTAINS NULL

B. SELECT customer_id, customer name FROM customers WHERE posta_code='_______'

C. SELECT customer_id, customer_name FROM customers WHERE postal_code IS NULL

D. SELECT customer_id, customer_name FROM customers WHERE postal code IS NVL

42) You added a PHONE-NUMBER column of NUMBER data type to an existing EMPLOYEES table. The EMPLOYEES table already contains records of 100 employees. Now, you want to enter the phone numbers of each of the 100 employees into the table some of the employees may not have a phone number available. Which data manipulation operation do you perform?

A. MERGE

B. INSERT

C. UPDATE

D. ADD

43) The CUSTOMERS table has these columns:

CUSTOMER_ID NUMBER (4) NOT NULL

CUSTOMER_NAME VARCHAR2 (100) NOT NULL

STREET_ADDRESS VARCHAR2 (150)

CITY_ADDRESS VARCHAR2 (50)

STATE_ADDRESS VARCHAR2 (50)

PROVINCE_ADDRESS VARCHAR2 (50)

COUNTRY_ADDRESS VARCHAR2 (50)

POSTE_CODE VARCHAR2 (12)

CUSTOMER_PHONE VARCHAR2 (20)

THE CUSTOMER_ID column is the primary key for the table which statement find the number of customer?

a)SELECT TOTAL (*) FROM customers;

b)SELECT COUNT (*) FROM customers;

c)SELECT TOTAL (customer_id) FROM customer;

d) SELECT COUNT(customers) FROM customers;

44) For which constraint does the Oracle Server implicitly create a unique index?

A. NOT NULL

B. PRIMARY KEY

C. FOREIGN KEY

D. CHECK

45) Which statement about subqueries are true?

A. A subquery should retrieve only one row.

B. A subquery can retrieve zero or more rows.

C. A subquery can be used only in SQL query statements.

D. Subqueries CANNOT be nested by more than two levels.

46) Which of the following queries can you use to search for employees with the pattern 'A_B' in their names?

A. SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\\';

B. SELECT last_name FROM employees WHERE last_name LIKE '%A_B%' ESCAPE;

C. SELECT last_name FROM employees WHERE last_name LIKE 'A_B%' ESCAPE '%';

D. SELECT last_name FROM employees WHERE last_name LIKE '%A\_B%' ESCAPE '\';

47) You own a table called EMPLOYEES with this table structure:

EMPLOYEE_ID NUMBER Primary Key

FIRST_NAME VARCHAR2 (25)

LAST_NAME VARCHAR2 (25)

HIRE_DATE DATE

What happens when you execute this DELETE statement?

DELETE employees;

A. You get an error because of a primary key violation.

B. The data and structure of the EMPLOYEES table are deleted.

C. The data in the EMPLOYEES table is deleted but not the structure.

D. You get an error because the statement is not syntactically correct.

48) What is necessary for your query on an existing view to execute successfully?

A. The underlying tables must have data.

B. You need SELECT privileges on the view.

C. The underlying tables must be in the same schema.

D. You need SELECT privileges only on the underlying tables.

49) A table can have _______ primary key

a)two

b) one

c) three

d) four

50) Examine the description of the EMPLOYEES table:

EMP_ID NUMBER (4) NOT NULL

LAST_NAME VARCHAR2 (30) NOT NULL

FIRST_NAME VARCHAR2 (30)

DEPT_ID NUMBER (2)

JOB_CAT VARCHAR2 (30)

SALARY NUMBER (8, 2)

Which statement shows the maximum salary paid in each job category of each department?

A. SELECT dept_id, job_cat, MAX (salary) FROM employees WHERE salary > MAX (salary);

B. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id, job_cat

C. SELECT dept_id, job_cat, MAX(salary) FROM employees;

D. SELECT dept_id, job_cat, MAX (salary) FROM employees GROUP BY dept_id;