ORacle
2. Create 'orders' table with the following columns: depno number/length=3 ordno number/length=6 orddesc alphanumberic/length=40 make 'ordno' as Primary key and 'depno' as Foreign key.
(ordno number(6) constraint ORD_PK primary key, depno number(3) constraint ORD_PK_DEPNO references DEPT(depno), orddesc varchar2(40));
10) What does the following SQL statement do?
Alter Table Customer Add Type (varchar2 (10));
11. Write a query and add a new column to employee table, and name it emp_salary with a data type number(20).
Alter table employee add emp_salary number(20);
18. Write a query and remove column emp_city from employee table.
Alter table employee drop column emp_city;
8) SELECT POWER(4,4) "Raised" FROM DUAL;
Answer: Raised ---------- 256
A) What are the results of the following queries? 6) SELECT ROUND(25.123,1) "Round" FROM DUAL;
Answer: Round ----------- 25.1
7) SELECT ROUND(43.129,2) "Round" FROM DUAL;
Answer: Round ------------ 43.13
9) select chr(61) from dual;
Answer: C - =
13) The result of "SELECT ADD_MONTHS('01-Feb-2019',2) FROM dual; " is -----------------.
Answer: 01-APR-19 OR 04/01/2019
5) The result for "SELECT POWER(5,3) FROM DUAL; " is ----------------- .
Answer: 125
4) The result for "SELECT ABS(-16.8) FROM DUAL; " is ----------------.
Answer: 16.8
6) The result for "select chr(50) from dual; " is -----------------------.
Answer: 2
14) The result of "select last_day('03-Mar-2019') from dual; " is -----------------.
Answer: 31-MAR-19 OR 03/31/2019
1) A database is an organized collection of ________ related data A) logically B) physically C) loosely D) badly
Answer: A
11) What does the following SQL command do? insert into Customer values (001,'John Smith','231 West St','Boston','MA','02115'); A) Adds a new record to the Customer B) Creates the Customer table C) Deletes the Customer table D) Updates the Customer table
Answer: A
17) The -------------- conditions specify an involving pattern matching. A) LIKE B) = C) None of the above D) Both of the above
Answer: A
20) The SQL GROUP BY clause can be used in a -------------- statement to collect data across multiple records and group the results by one or more columns. A) SELECT B) DELETE C) DROP D) ALTER
Answer: A
5) ________ is a set of commands used to update a database. A) DML B) DDL C) DCL D) DPL
Answer: A
3) ---------- returns the absolute value of n.
Answer: ABS
11) ------------- returns the decimal representation in the database character set of the first character of the result.
Answer: ASCII
1) A Single-instance Oracle database configuration means a --------------- relationship exists between the database and an instance. A) One-to-many B) One-to-one C) Both of the above D) None of the above
Answer: B
11) An ---------- is an optional structure, associated with a ---------, and it speeds data access. A) Table, index B) Index, table C) Index, view D) View, index
Answer: B
13) ---------------------- statement is used to delete a single record or multiple records from a table. A) Drop B) Delete C) Truncate D) All of the above
Answer: B
13) INSERT INTO is an example of ________ code. A) DDL B) DML C) DCL D) DNL
Answer: B
15) Which of the following statement retrieves data from a table? A) Insert B) Select C) Alter D) Update
Answer: B
17) A primary key that consists of more than one attribute is called a: A) foreign key. B) composite key. C) multivalued key. D) cardinal key.
Answer: B
2) ________ are established between entities in a well-structured database so that the desired information can be retrieved. A) Entities B) Relationships C) Lines D) Ties
Answer: B
3) The ________ is the structure that contains descriptions of objects such as tables and views created by users. A) SQL B) schema C) catalog D) master view
Answer: B
3) Which of the following special character cannot be part of a table name? A) $ (Dollar sign) B) - (Dash) C) _ (Underscore) D) # (Hash)
Answer: B
5) Which one is not a valid table name in an Oracle database? A) Emp_billing B) Emp-billing C) Emp$billing D) Emp#billing
Answer: B
6) DDL is typically used during which phase of the development process? A) Implementation B) Physical design C) Analysis D) Evaluation
Answer: B
6) What is the maximum length of a table name in Oracle 11g? A) 128 B) 30 C) None of the above D) Both A and C
Answer: B
7) Any create command may be reversed by using a(n) ________ command. A) undo B) drop C) delete D) unpack
Answer: B
8) The SQL command ________ adds one or more new columns to a table. A) create table B) alter table C) create view D) create relationship
Answer: B
12) Referential Integrity Constraints are generally established between: A) Referential and Integrity keys. B) Primary and Secondary keys. C) Primary and Foreign keys. D) Foreign and Domestic keys.
Answer: C
14) In an SQL statement, which of the following parts states the conditions for row selection? A) Select B) From C) Where D) Group By
Answer: C
15) An attribute (or attributes) that uniquely identifies each row in a relation is called a: A) column. B) foreign field. C) primary key. D) duplicate key.
Answer: C
16) An attribute in a relation of a database that serves as the primary key of another relation in the same database is called a: A) link attribute. B) link key. C) foreign key. D) foreign attribute.
Answer: C
19) Which clause is used to sort the records in your result set. A) Group by B) Sort by C) Order by D) None of the above
Answer: C
4) Which one is a valid table name in an Oracle database? A) Customer Orders B) Order C) Orders D) 2Order
Answer: C
4) ________ is a set of commands used to control a database, including security. A) DML B) DDL C) DCL D) TCL
Answer: C
7) Which of the following is not a valid oracle data type? A) Number B) Varchar2 C) Alphanumeric D) Date
Answer: C
9) A ------------- is a logical representation of another table or combination of tables. A) Virtual table B) View C) Both of the above D) None of the above
Answer: C
A) Alters the Customer table to accept Type Varchar2 B) Alters the Customer table to be a Type 2 Varchar C) Alters the Customer table, and adds a field called "Type" D) Alters the Customer table by adding a 2-byte field called "Varchar"
Answer: C
12) ---------------- returns the current date and time set for the operating system on which the database resides.
Answer: CURRENT_TIMESTAMP
7) " SELECT CHR(67)||CHR(108)||CHR(97)||CHR(115)||CHR(115) FROM DUAL;" will return ---------.
Answer: Class
10) Which one is not an advantage of views? A) Enhance programming productivity B) Use little storage space C) Simplify query commands D) Contain old data of a table
Answer: D
12) ---------------- lists the column definitions. A) Desc B) Describe C) None of the above D) Both of the above
Answer: D
14) Which of the following is an inequality operator? A) != B) ^= C) <> D) All of the above
Answer: D
16) Which of the following object will be created by default for the primary key column if you define a primary key on a table? A) View B) Sequence C) Trigger D) Index
Answer: D
18) ------------------------statement is used to add, modify, or drop/delete columns in a table. A) Update table B) Delete table C) Truncate table D) Alter table
Answer: D
18) A ________ defines or constrains some aspect of the business. A) Business constraint B) Business structure C) Business control D) Business rule
Answer: D
2) Which of the following is not part of the Oracle instance? A) Memory B) Shared pool C) Buffer cache D) Datafile
Answer: D
8) -------------------- is the basic unit of data storage in an Oracle Database. A) Index B) Block C) View D) Table
Answer: D
9) How to make changes permanent after manipulating a table? A) Rollback B) Update C) Delete D) Commit;
Answer: D
28) The DROP command deletes rows from a table individually or in groups.
Answer: FALSE DELETE
19) DCL is used to update the database with new records.
Answer: FALSE DML
25) The DELETE TABLE DDL command is used to remove a table from the database.
Answer: FALSE DROP
22) The CREATE SCHEMA , DDL command is used to create a table.
Answer: FALSE TABLE not Schema
24) In databases, null values are equivalent to zero.
Answer: FALSE is not
30) RDBMS stands for Related Data Build Management System.
Answer: FALSE its Relational Database Management System
21) A database is maintained and queried using the data mapping manipulation language (DML).
Answer: FALSE manipulation not mapping
27) The following insert command would work fine: insert into budget values 121,222,111;
Answer: FALSE parenthesis needed
8) "SELECT INITCAP('I am a student.') FROM DUAL; " will return ----------------------.
Answer: I Am A Student.
15) --------------function returns the number of months between date1 and date2.
Answer: MONTHS_BETWEEN
2) ---------------- functions accept numeric input and return numeric values.
Answer: Numeric
10) "SELECT UPPER(SUBSTR('interesting',3,4)) FROM DUAL; " will return -----------------.
Answer: TERE
20) A database table is defined using the data definition language (DDL).
Answer: TRUE
23) SQL is a standard language for database access.
Answer: TRUE
26) The SQL command used to populate tables with data is the INSERT command.
Answer: TRUE
29) The asterisk (*) wildcard designator can be used to select all fields from a table as well as in WHERE clauses when an exact match is not possible.
Answer: TRUE
16) The general comparison functions determine the ------------and/or -----------value from a set of values.
Answer: greatest, least
9) "select lower('WORLD') from dual; " will return -------------------------------.
Answer: world
15. Write a query to remove the records of employee with the zip code of 91016.
Delete from employee where emp_zip=91016;
19. Write a query and remove all the data in employee table.
Delete from employee;
20. Write a query to remove the employee table.
Drop table employee;
4) SELECT id, SUBSTR(name,2,2) "Substring" FROM employee;
ID | Substring | ----------------- 8 | un 1 | oh 3 | od 6 | un 9 | ne 2 | ar 4 | am 5 | at 7 | ar 10| ub
B) Delete one record from 'dept' table
In order to delete a department, first remove all records from orders which are related to that specific department: delete from orders where ordno=100; delete from dept where ordno=10;
9. Write a query and add one record into emp_id, emp_name, dept_id columns in table.
Insert into employee (emp_id, emp_name, dept_id) values (100, 'Jack Smith', 10);
10. Write a query and add one record in all columns in Employee table.
Insert into employee values (101, 'Sara Beteta ', '123 Santa anita ave ', 90107, 'Pasadena ', 'CA ', 10);
5) SELECT name, gender FROM employee WHERE ASCII(SUBSTR(name, 1, 1)) = 83;
NAME | GENDER -------------------------- Sunitha | Female Sunil | Male Sneha | Female
7. Write a SELECT statement to list the information of employees whose id is less than or equal 250.
Select * from employee where emp_id <=250;
6. Write a SELECT statement to list all employees that live either in zip code 91106, 91023, or 11430.
Select * from employee where emp_zip in (91106,91023,11430); OR Select * from employee where emp_zip=91106 or emp_zip=91023 or emp_zip=11430;
13. Write a query to see all records of employee table.
Select * from employee;
14. Write a query to find out the number of records in employee table.
Select count(*) from employee;
2. Write a SELECT statement to list the address of employees that do not have the department ID equals 100.
Select emp_address from employee where dept_id<>100;
5. Write a SELECT statement to list the cities which zip codes include 11 in it.
Select emp_city from employee where emp_zip like '%11%';
4. Write a SELECT statement to list the employees name whose address start with 234 duarte.
Select emp_name from employee where emp_address like '234 duarte%';
8. Write a SELECT statement to list employee name and address where name ends with 'M'.
Select emp_name, emp_address from employee where emp_name like '%M';
3) SELECT UPPER (name) from employee where salary=3000;
UPPER(NAME) -------------- SNEHA MARY
16. Write a query to change the name of the city to 'Pasadena' where zip code is 91107.
Update employee set emp_city= 'Pasadena ' where emp_zip=91107;
17. Write a query to change the name of the city to 'Pasadena' and the zip code to 91107 where zip code is 91006.
Update employee set emp_city='Pasadena' , emp_zip=91107 where emp_zip=91006;
12. Write a query and update the salary for employee by id equal 100 to $50000.
Update employee set emp_salary=50000 where emp_id=100;
1. Run the queries from PPT and create 'dept' table, make depno as primary key, and insert some dat
a. (depno number(3), depname varchar2(15), location varchar2(15)); Alter table DEPT add constraint DEPT_PK Primary key (DEPNO); insert into dept values( 10,'sales','pasadena'); insert into dept values( 20,'shipping','pasadena');
3. Write a SELECT statement to list the id of employees who doesn't live in Los Angeles and Pasaden
a. Select emp_id from employee where emp_city not in ('Los Angeles', 'Pasadena'); OR Select emp_id from employee where emp_city <>'Los Angeles ' and emp_city <>'Pasadena ';
1. Write a SELECT statement to list zip codes and their cities in Californi
a. Select emp_zip , emp_city from employee where emp_state= 'CA' ;
3. Write queries to: A) Insert 5 records in 'orders' table
insert into orders values (100,10, 'Ship the next day'); insert into orders values (101,20, 'New order'); OR insert into orders (ordno, depno, orddesc) values (200,20, 'Same day delivery');
1) SELECT CONCAT(CONCAT(CONCAT(name, ' is from '),country),'.') "phrase" FROM employee WHERE gender= 'Female';
phrase ------------------------- Sunitha is from Indea. Sneha is from Indea . Mary is from Indea . Pam is from Indea .
C) Remove the description of one record from 'orders' table.
update orders set orddesc= ' ' where ordno=101; OR update orders set ord_desc= null where ordno=101;
2) SELECT LOWER(country) "value" FROM employee;
value -------- indea uk uk usa indea indea indea usa uk uk