Chapter 5 Review Questions

What is the purpose of the CREATE VIEW and DROP VIEW commands?

A view is a mechanism that allows the structure of a query to be saved in the RDBMS. It is not an actual table and does not physically save data. When view is invoked it executes a query that retrieves the data from the actual tables. ex. CREATE VIEW products_more_than_3_sold AS SELECT productid, productname, productprice FROM product WHERE productid IN (SELECT productid FROM soldvia GROUP BY productid HAVING SUM(noofitems) >3); ex. DROP VIEW products_more_than_3_sold;

What aggregate functions are provided by SQL?

COUNT, SUM, AVG, MIN, and MAX Ex. SELECT COUNT(*), AVG(productprice), MIN(productprice), MAX (productprice) FROM product WHERE categoryid = 'CP';

Difference between DCL and TCL

DCL (Data control language) commands facilitate the process of data access control. TCL (transaction control language) is used to manage database transactions

What is the purpose of DDL SQL statements?

Data definition language is to create and modify the structure of the database. Its purpose is to enable the implementation of the relational schema (and other structures like indexes and constraints) as an actual relational database Ex. CREATE, ALTER, DROP

What is the purpose of DML SQL statements?

Data manipulation language statements are used to manipulate the data within the database. Its purpose is to insert, modify, delete, and retrieve the data from the database. Ex. INSERT INFO, UPDATE, DELETE, SELECT

What is the purpose of the HAVING clause?

It determines which groups will be displayed in the result of a query and consequently, which groups will not be displayed in the result of the query. Note: a HAVING clause MUST contain a GROUP BY clause. Ex. SELECT vendorid, categoryid, COUNT(*), AVG(productprice) FROM product WHERE productprice >=50 GROUP BY vendorid, categoryid HAVING COUNT(*) >1;

What is the purpose of the WHERE command?

It determines which rows should be retrieved and consequently which rows should not be retrieved. It is a condition within a SELECT statement. Ex. SELECT productid, productname, vendorid, productprice FROM product WHERE productprice > 100;

What is the purpose of the GROUP BY clause?

It enables summarizations across the groups of related data within tables. Aggregate functions are often used in conjunction with this keyword. Ex. SELECT vendorid, COUNT(*), AVG (productprice) FROM product GROUP BY vendorid;

What is the purpose of DISTINCT keyword?

It is a keyword that can be used in a conjunction with the SELECT statement. Use this keyword if you want to show the values that exist in the table without repeating them multiple times Ex. SELECT DISTINCT vendorid FROM product;

What is the purpose of OUTER JOIN?

It is a variation of the JOIN operation that supplements the results with the records from one relation that have no match in the other relation. 3 variations: LEFT OUTER JOIN, RIGHT OUTER JOIN, AND FULL OUTER JOIN ex. SELECT a.buildingid, a.aptno, c.ccname FROM apartment a LEFT OUTER JOIN corpclient cON a.ccid = c.ccid;

What is the purpose of an alias?

It is an alternative and/or shorter name of each relation that is part of the FROM part of the query. Its purpose is to be used instead of the full relation name. Ex. SELECT p.productid, p.productname, v.vendorname, p.productprice FROM product p, vendor v WHERE p.vendorid = v.vendorid;

What is the purpose of SELECT command?

It is the most commonly used command. It is used for the retrieval of data from the database relations. The result is a table listing the records requested by the command. Ex. Select <columns/fields> From <table>

What is the purpose of the CREATE TABLE command?

It is used for creating and connecting relational tables.

What is the purpose of the DELETE command?

It is used for deleting the data stored in database relations. ex. DELETE FROM product WHERE productid = '7x7';

What is the purpose of the UPDATE command?

It is used for modifying the data stored in database relations. Ex. INSERT INTO product VALUES ('7x7', 'AirySock', 1000, 'MK', 'FW'); Update: UPDATE product SET productprice = 10 WHERE productid = '7x7';

What is the purpose of the IN keyword?

It is used in SQL for comparison of a value with a set of values. Ex. SELECT productid, productname, productprice FROM product WHERE productid IN (SELECT productid FROM soldvia GROUP BY productid HAVING SUM(noofitems) > 3) ;

What is the purpose of ALTER TABLE command?

It is used in cases when we want to change the structure of the relation, once the relation is already created. Ex. we want to add in an optional column VendorPhoneNumber to the relation VENDOR ALTER TABLE vendor ADD ( vendorphonenumber CHAR(12));

What is the purpose of the NOT keyword?

It is used in conjunction with the condition comparison statements returning the Boolean values TRUE or FALSE ex. SELECT * FROM bulding b WHERE NOT EXISTS (SELECT * FROM manager m WHERE b.buildingid = m.mresbuildingid);

What is the purpose of the IS NULL keyword?

It is used in queries that contain comparisons with an empty value in a column of a record SELECT * FROM manager WHERE mbonus IS NULL;

What is the purpose of the EXSITS keyword?

It is used to check if the result of the inner correlated query is empty ex. SELECT * FROM building b WHERE EXISTS (SELECT * FROM manager m WHERE b.buildingid = m.mresbuildingid);

What is the purpose of JOIN condition?

It is used to facilitate the querying of multiple tables. Ex. SELECT productid, productname, vendorname, productprice FROM product, vendor WHERE product.vendorid=vendor.vendorid;

What is the purpose of the INSERT INFO command?

It is used to populate the created relations with data.

What is the purpose of the DROP TABLE command?

It used to remove a table from the database. Note: you have to drop the tables that have the foreign key before you can drop the tables to which the foreign keys refer

What is the purpose of the LIKE keyword?

Its purpose is to retrieve the records whose values partially match a certain criteria Ex. SELECT * FROM product WHERE productname LIKE '%Boot%' ;

What is the purpose of the ORDER BY clause?

Its purpose is to sort the results of the query by one or more columns. Ex. SELECT productid, productname, categoryid, productprice FROM product WHERE categoryid = 'FW' ORDER BY productprice;

What set operators are available in SQL?

Union, intersection, and difference. They are used to combine the results of two or more SELECT statements that are union compatible.

What is a nested query?

it is a query that is used within another query. There is the inner query, which is the nested query and the outer query, which is the query that uses the nested query. Ex. SELECT productid, productname, productprice FROM product WHERE product price < (SELECT AVG(productprice) FROM product) ;