Lesson 3 Basic SQL

What is the difference between the UPDATE and INSERT commands?

INSERT creates new rows in the table, while UPDATE changes rows that already exist.

Define a database schema.

- A schema is a logical group of database objects - such as tables and indexes - that are related to each other. The schema usually belongs to a single user or application. A single database can hold multiple schemas that belong to different users or applications.

Define the DISTINCT clause.

- A simple listing (SELECT) is not very useful if the table contains several thousand rows and you have to sift through the vendor codes manually. The DISTINCT clause produces a list of only those values that are different from one another, example: SELECT DISTINCT V_CODE FROM PRODUCT;

Define how you can add composite PKs and multiple FKs in a single SQL command.

- ALTER TABLE LINE ADD PRIMARY KEY (INV_NUMBER, LINE_NUMBER) ADD FOREIGN KEY (INV_NUMBER) REFERENCES INVOICE ADD FOREIGN KEY (P_CODE) REFERENCES PRODUCT;

Define how to re-establish the integrity rules by using the ALTER command.

- ALTER TABLE TABLENAME ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;

Define how you would add a PK.

- ALTER TABLE tablename ADD PRIMARY KEY (ATABLENAME_CODE);

Depict how you would remove a column.

- ALTER TABLE tablename DROP COLUMN COLUMNNAME;

Define how you would remove a column or table constraint.

- ALTER TABLE tablename DROP {PRIMARY KEY | COLUMN columnname | CONSTRAINT constraintname };

Define how you would add or modify columns.

- ALTER TABLE tablename {ADD | MODIFY} ( columnname datatype [ {ADD | MODIFY} columnname datatype ] );

How do you save table changes?

- COMMIT [WORK] or COMMIT; (saves everything)

Before you can create a new RDBMS, you must complete two tasks:

- Create the database structure - Create the tables that will hold the end-user data

Data manipulation language.

- Data manipulation language: Includes commands to insert, update, delete, and retrieve data within the database tables. It refers to a language that describes the database structures. The commands include things like: GROUP BY, DESCRIBE, DELETE, WHERE, etc.

Define how to remove a table from a database.

- Delete a table from the database using the DROP TABLE command; example: DROP TABLE tablename; You can only drop the table if it is not on the "one" side of any relationship. If you try to drop a table otherwise, the RDBMS will generate an error message indicating that a FK integrity violation has occurred.

Define the outer query.

- Executed last - remember that the outer query is always the first SQL command you encounter and in this case it is SELECT. The following command sequence - note that the inner query first finds the maximum price value, which is stored in memory. Because the outer query now has a value to which to compare each P_PRICE value, the query executes properly. SELECT P_CODE, P_DESCRIPT, P_PRICE FROM PRODUCT WHERE P_PRICE = (SELECT MAX(P_PRICE) FROM PRODUCT);

Define GROUP BY clause.

- Generally used when you have attribute columns combined with aggregate functions in the SELECT statement. Valid only when used in conjunction with one of the SQL aggregate functions, such as COUNT, MIN, MAX, AVG, and SM.

Depict what the INSERT command looks like when entering numeric and null values.

- INSERT INTO PRODUCT VALUES ('BRT-345', 'Titanium drill bit', 45, 64, NULL);

Depict how you would copy some parts of a table.

- INSERT INTO target_tablename [(target_columnlist )] SELECT source_columnlist FROM source_tablename;

Define data definition language.

- Includes commands to create database objects such as tables, indexes, and view, as well as commands to define access rights to those database objects. It changes stored data but not the data objects. The commands include things like: CREATE TABLE, NOT NULL, UNIQUE, etc.

Define what a cascading order sequence is.

- Multileveled ordered sequence is known as a cascading order sequence and it is created by listing several attributes, separated by commas, after the ORDER BY clause.

Define HAVING clause.

- Operates like the WHERE clause in the SELECT statement, however, the WHERE clause applies to columns and expressions for individual rows, while the HAVING clause is applied to the output of a GROUP BY operation. Example: SELECT V_CODE, SUM(P_QOH * P_PRICE) AS TOTCOST FROM PRODUCT GROUP BY V_CODE HAVING (SUM(P_QOH * P_PRICE) > 500) ORDER BY SUM(P_QOH * P_PRICE) DESC;

How do you restore table contents and delete table rows?

- ROLLBACK; and the DELETE syntax is: DELETE FROM tablename [WHERE conditionlist];

What does a conditional restriction look like?

- SELECT columnlist FROM tablelist [WHERE conditionlist]; The restriction is always placed right by the WHERE clause. You can add conditionals like =, <>, etc.

Define the AVG function.

- Similar to those of MIN and MAX and is subject to the same operating restrictions. Example: SELECT AVG(P_PRICE) FROM PRODUCT;

What type of query is always executed first?

- Subquery aka nested or inner query

What command changes a table and what options are available to make specified changes in conjunction with that initial command?

- The command is ALTER TABLE and the options include ADD, MODIFY, and DROP (which all correlate to columns/attributes).

Define the SELECT command.

- Used to list the contents of a table. The syntax of the SELECT command is as follows: SELECT columnlist FROM tablename

Define when and how to use the ORDER BY clause.

- Useful when the list order is important to you. Syntax: SELECT columnlist FROM tablelist [WHERE conditionlist ] [ORDER BY columnlist [ASC | DESC] ];

Define the COUNT function.

- Uses one parameter within parentheses, generally a column name such as COUNT(V_CODE) or COUNT(P_CODE). The parameter can also be an expression, i.e.; COUNT(DISTINCT V_CODE) or COUNT(P_PRICE+10).

How do you join database tables?

- You must simply list the tables in the FROM clause of the SELECT statement. The DBMS will create the Cartesian product of every table in the FROM clause. However, to get the correct result-that is, a natural join-you must select only the rows in which the common attribute values match. To do this, use the WHERE clause to indicate the common attributes used to link the tables; this WHERE clause is sometimes referred to as the join condition.

What is a recursive join?

A recursive join is a join in which a table is joined to itself.

Explain the difference between an ORDER BY clause and a GROUP BY clause.

An ORDER BY clause has no impact on which rows are returned by the query, it simply sorts those rows into the specified order. A GROUP BY clause does impact the rows that are returned by the query. A GROUP BY clause gathers rows into collections that can be acted on by aggregate functions.

What is the difference between the COUNT aggregate function and the SUM aggregate function?

COUNT returns the number of values without regard to what the values are. SUM adds the values together and can only be applied to numeric values.