Database-chap 8

Question 1

SQL functions fit into two broad categories

Answer 1

Data definition language
Data manipulation language

Question 2

Data definition language

Answer 2

SQL includes commands to:
-Create database objects, such as tables, indexes, and views
-Define access rights to those database objects

Question 3

Data manipulation language

Answer 3

Includes commands to insert, update, delete, and retrieve data within database tables

Question 4

Data Types

Answer 4

Data type selection is usually dictated by nature of data and by intended use

Question 5

SQL Constraints

Answer 5

-NOT NULL constraint
Ensures that column does not accept nulls
-UNIQUE constraint
Ensures that all values in column are unique
-DEFAULT constraint
Assigns value to attribute when a new row is added to table
-CHECK constraint
Validates data when attribute value is entered e.g min order

Question 6

SQL Indexes

Answer 6

When primary key is declared, DBMS automatically creates unique index

Question 7

Create index

Answer 7

Using CREATE INDEX command, SQL indexes can be created on basis of any selected attribute

CREATE UNIQUE INDEX indexname
ON tablename (column1);

Question 8

Data Manipulation Commands

Answer 8

Adding table rows
Saving table changes
Listing table rows
Updating table rows
Restoring table contents
Deleting table rows
Inserting table rows with a select subquery

Question 9

Adding Table Rows notice that:

Answer 9

-Row contents are entered between parentheses
-Character and date values are entered between apostrophes
-Numerical entries are not enclosed in apostrophes
-Attribute entries are separated by commas
-A value is required for each column
-Use NULL for unknown values

Question 10

Insert

Answer 10

Used to enter data into table
Syntax:
INSERT INTO TABLE VALUES (value1, value2, … , valuen);

Question 11

Commit

Answer 11

Will permanently save any changes made to any table in the database

COMMIT[WORK];

Question 12

Changes made to table contents are not physically saved on disk until, one of the following occurs:

Answer 12

-Database is closed
-Program is closed
-COMMIT command is used

Question 13

SELECT

Answer 13

-Used to list contents of table
Syntax:
SELECT columnlist FROM tablename;

Question 14

UPDATE

Answer 14

Syntax:
UPDATE tablename SET columnname = expression columname = expression columnname WHERE conditionlist];

Question 15

ROLLBACK

Answer 15

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

Syntax:
ROLLBACK;

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

Question 16

Delete

Answer 16

Deletes a table row
Syntax:
DELETE FROM tablename[WHERE conditionlist ];

Question 17

Inserting Table Rows with a Select Subquery

Answer 17

INSERT
-Inserts multiple rows from another table (source)
-The values returned by the SELECT subquery must match the attributes and data types of the table in the INSERT statement
-Uses SELECT subquery
Query that is embedded (or nested) inside another query
Executed first

Question 18

Selecting Rows with Conditional Restrictions

Answer 18

Syntax:
SELECT columnlist
FROM tablelist
[ WHERE conditionlist ] ;

Question 19

Selecting Rows with Conditional Restrictions

Answer 19

Syntax:
SELECT columnlist
FROM tablelist
[ WHERE conditionlist ]

Question 20

Arithmetic Operators: The Rule of Precedence

Answer 20

-Perform operations within parentheses
-Perform power operations
-Perform multiplications and divisions
-Perform additions and subtractions

Question 21

Special Operators

Answer 21

-BETWEEN
Used to check whether attribute value is within a range
-IS NULL
Used to check whether attribute value is null
-LIKE
Used to check whether attribute value matches given string pattern
-IN
Used to check whether attribute value matches any value within a value list
-EXISTS
Used to check if subquery returns any rows

Question 22

Advanced Data Definition Commands

Answer 22

All changes in table structure are made by using ALTER command
Followed by keyword that produces specific change
Following three options are available:
ADD
MODIFY
DROP
ALTER TABLE PRODUCT
{ADD PROD_NAME};

Question 23

Changing a Column’s Data Type

Answer 23

ALTER can be used to change data type
ALTER TABLE PRODUCT
MODIFY (V_CODE CHAR(5));

Question 24

Dropping a Column

Answer 24

Use ALTER to drop column
-Some RDBMSs impose restrictions on the deletion of an attribute
E.G. ALTER TABLE VENDOR
DROP COLUMN V_ORDER;

Question 25

Copying Parts of Tables

Answer 25

SQL permits copying contents of selected table columns so that the data need not be reentered manually into newly created table(s) CREATE TABLE PART(

Question 26

Adding Primary and Foreign Key Designations

Answer 26

When table is copied, integrity rules do not copy, so primary and foreign keys need to be manually defined on new table Syntax: ALTER TABLE tablename ADD PRIMARY KEY(fieldname); For foreign key, use FOREIGN KEY in place of PRIMARY KEY

Question 27

Deleting a Table from the Database

Answer 27

DROP Deletes table from database Syntax: DROP TABLE tablename; DROP TABLE PART;

Question 28

Advanced Select Queries

Answer 28

SQL provides useful functions that can: Count Find minimum and maximum values Calculate averages

Question 29

Grouping Data

Answer 29

SELECT PROD_CODE, AVG(PROD_PRICE) FROM PRODUCT GROUP BY PROD_PRICE

Question 30

HAVING clause

Answer 30

Applied to the output of a GROUP BY operation SELECT PROD_CODE, AVG(PROD_PRICE) FROM PRODUCT GROUP BY PROD_PRICE HAVING AVG (PROD_PRICE) < 10;

Question 31

ORDER BY clause

Answer 31

-Used to sort output of SELECT statement -Can sort by one or more columns and use either an ascending or descending order