ITDB202 - Structured Query Language Semester II, AY 2020-2021 ACTIVITY 1 - 5 MARKS Student ID Date: Student Name Section 3 answer. MCQ. Read the statements and encircle the letter of the correct 1. You are task to determine the structure of the EMPLOYEES table, what will be the correct SQL statement? A SELECT * FROM Employees; C. DESC * from Employees; B. SELECT employee_id From Employees, D. DESC Employees: 2. Which of the following statements will execute correctly? A. CREATE TABLE emp (ID NUMBER(5) PRIMARY KEY, Name VARCHAR2(30): B. CREATE TABLE emp (ID NUMBER(S), PRIMARY KEY, Name, VARCHAR2(30)); C. CREATE TABLE emp (ID NUMBER(5) PRIMARY KEY, Name VARCHAR2(30)); D. CREATE TABLE emp (ID NUMBER(5) PRIMARY KEY, Name VARCHAR(30)); 3. The HR wants to retrieve the information of the employees who is earning within the range of 8000 to 15000. The report includes the following information such as the employee id, last name and salary. Which of the following SQL statements below satisfies the condition? A. SELECT employee_id, last_name, salary FROM Employees WHERE salary IN 8000 AND 15000; B. SELECT employee_id, last_name, salary FROM Employees WHERE salary IN (8000, 15000); C. SELECT employee_id, last_name, salary FROM Employees WHERE salary BETWEEN 15000 TO 8000; D. SELECT employee_id, last_name, salary FROM Employees WHERE salary BETWEEN 8000 AND 15000; 4. Which of the following WHERE clauses contains an error? The SELECT and FROM clauses are SELECT * FROM EMPLOYEES A. WHERE HIRE DATE IN ('02-JUN-2004); C. WHERE JOB ID IS NULL; B. WHERE SALARY IN (1000 OR 4000); BETWEEN 101 AND 105; D. WHERE ID 5. Consider the following table and SQL command: PRODUCTS Feld Name Data Type Sire Constraint PROD ID Numer (5) PUIMARY KEY PROD NAME Vacher) NOT NULL PRICE Number, 2PRIE > CREATE TABLE PRODUCTS (ID NUMBER(5), Prod_Name VARCHAR2(30) UNIQUE, Price NUMBER(5) (PRICE >0)); Find the errors in the statement above. Correct the errors and write the correct SQL statement below: 6. Write the syntax for renaming a column in an existing table: Refer to the EMPLOYEES table: EMPLOYEES impompomelo MerreDate Sal Com Dept. 100 KING PRESIDENT 17-JUN-2001 KOCHAR MANAGER 100 1170006710 102 VARGAS MANAGERINANI 0.64 100 MATOS VADNO I SIN 7. What is the output for the following queries based from the given table? a. SELECT EmpName, Sal FROM employees WHERE Sal > 17000 or DeptNo 10; b. SELECT EmpName, Sal, Job FROM employees WHERE EmpName LIKE' a'AND Sal> 10000: DDL, DML and SELECT Statements 8. Create the BOOKS table: Column Name Data Type Sine Constraints HID Numer 5 PRIMARY KEY Catery Varchar? 30 NOT NULL Title Varch2 30 NOT NULL Price Number 5.2 9. Add the new column Author in BOOKS table, string value up to 30 characters. 10. Insert the following records to BOOKS table: Bali Category Tale Price Auto 1 IT SOLS0 Khalid 2 TD75 A 1 BS hics 60 Mura 4 Matt Calculus 65 A 11. Write a SQL Command to save the records permanently. 12. Change the Price of Book 3 to 90 in the BOOKS table. 13. Change the Author of Book 1 to Mazin in the BOOKS table. 14. Display the unique author in the BOOKS table. 15. Display the Price of all books with increase by 10, use the alias "Increased by 10 rials". 16. Display only the books from IT and Math categories. 17. Display the Book ID, title, and price of all books which is written by Amar or whose price is more than 60 rials 18. Display the title, category, and price of all books which price is in the range of 60 to 70 19. Display all books whose title starts with 'D'. 20. Display the Book ID, Title, and author of all books who has author. Arrange the records in ascending order by author's name
