Unit 9 : SQL ( Part 1 )

Question 1

List out 2 broad categories of SQL Functions

Answer 1

1. Data Definition Language ( DDL )
   
   • Create Database, Tables, Indexs and Views
   • Define access rights to those database objects
2. Data Manipulation Language ( DML )
   
   • Commands to insert, update, delete and retireve data within database tables

Question 2

How many words does basic command set has ?

Answer 2

1. Less than 100 words

Question 3

Who prescribes a standard SQL?

Answer 3

1. American National Standards Institute ( ANSI )

Question 4

Will there be several SQL dialects exists ?

Answer 4

1. Yes

Question 5

List out 2 tasks must be completed before creating a database?

Answer 5

1. Create database structure
2. Create tables that will hold end-user data

• RDBMS creates physical files that will hold database
• RDBMS tends to differ from one to another

Question 6

What is schema?

Answer 6

1. Group of database objects , such as tables and indexes that are related to each other

Question 7

How does Authentication process through DBMS? ( 2 )

Answer 7

1. Verifies taht only registered users are able to access database
2. Log on to RDBMS using user id and password created by database administrator

Question 8

What is data type selection is usually dictated by ?

Answer 8

1. Nature of data and by intended use

• Need to pay close attention to expected use of attributes for sorting and data retrieval purposes

Question 9

What uses DECIMAL (L,D) for data types?

Answer 9

1. Price

Question 10

What use INTEGER for data types?

Answer 10

1. Whole numbers

Question 11

List out 2 data types for Character

Answer 11

1. CHAR(L)
2. VARCHAR(L) or VARCHAR2(L)

• Oracle users may use VARCHAR2 as well as VARCHAR

Question 12

List out 4 data types for Character

Answer 12

1. NUMBER(L,D)
2. INTEGER
3. SMALLINT
4. DECIMAL(L,D)

Question 13

List out data types for date

Answer 13

1. DATE

Question 14

What specification does Primary Key contains ? ( 2 )

Answer 14

1. NOT NULL
2. UNIQUE

Question 15

What will RDBMS do after creating Primary Key?

Answer 15

1. Automatically enforce referential integrity for foreign keys

Question 16

What symbol should we use when the command sequence ends?

Answer 16

1. Semicolon ( ; )

Question 17

What does NOT NULL constraint ensures?

Answer 17

1. Ensures that column does not accept nulls

Question 18

What does UNIQUE constraint ensures?

Answer 18

1. Ensures that all values in column are unique

Question 19

What does DEFAULT constraint assigns?

Answer 19

1. Assigns value to attribute when a new row is added to table

Question 20

What does CHECK constraint validates

Answer 20

1. Validates data when attribute value is entered

Question 21

List out the commands with Data Manipulation ( 7 )

Answer 21

1. Adding table rows
2. Saving table changes
3. Listing table rows
4. Updating table rows
5. Restoring table contents
6. Deleting table rows
7. Inserting table rows with a select subquery

Question 22

How to insert values ? ( Syntax )

Answer 22

INSERT INTO columnname
VALUES ( value1 , value2 , … , valuen )

Question 23

When should we use NULL?

Answer 23

1. NULL for unknown values

Question 24

List out 3 things that lets database save physically on disk

Answer 24

1. Database is closed
2. Program is closed
3. COMMIT command is used

COMMIT [WORK]; ( Syntax )
* It will permanently save any changes made to any table in the database

Question 25

What does SELECT used for?

Answer 25

1. Used to list contents of table

SELECT columnlist ( attributes )
FROM tablename;

SELECT * FROM tablename;

Question 26

What does UPDATE do? Can list out the syntax?

Answer 26

1. Modify data in table

UPDATE tablename
SET columnname = expression
WHERE conditionlist;

• If more than one attribute is to be updated in row, separate corrections with commas

UPDATE tablename
SET columnname = expression , columnname = expression
WHERE conditionlist;

Question 27

What does ROLLBACK does?

Answer 27

1. Used to restore database to its previous condition
2. Only applicable if COMMIT command has not been used to permanently store changes in database

• ROLLBACK
• Both COMMIT and ROLLBACK only work with data manipulation commands that are used to add, modify, or delete table rows

Question 28

What does DELETE does?

Answer 28

1. Deletes a table row

DELETE FROM tablename
WHERE conditionlist

• If WHERE condition is not specified, all rows from specified table will be deleted

Question 29

How to select rows with conditional restrictions?

Answer 29

SELECT columnlist
FROM tablelist
WHERE conditionlist;

Question 30

What are the symbols for comparison operators ? ( 2 )

Answer 30

1. <>
2. !=

Question 31

List out 4 rules for arithmetic operators?

Answer 31

1. Perform operations within parentheses
2. Perform power operations
3. Perform multiplications and divisions
4. Perform additions and subtractions

Question 32

What are the symbols for power? ( 2 )

Answer 32

2. **

Question 33

What is between used for?

Answer 33

1. Used to check whether attribute value is within a range

Question 34

What is IS NULL used for?

Answer 34

1. Used to check whether attribute value is null

Question 35

What is LIKE used for?

Answer 35

1. Used to check whether attribute value matches given string pattern

Question 36

What is IN used for?

Answer 36

1. Used to check whether attribute value matches any value within a value list

Question 37

What is EXISTS used for?

Answer 37

1. Used to check if subquery returns any rows