Upgrade to remove ads
Terms in this set (59)
SQL relational set operators
DIVIDE (not a command but can be done with other 7)
Two set of commands for SQL
Data definition language (DDL)
Data manipulation language (DML)
Data definition language (DDL)
SQL commands such as CREATE TABLE, NOT NULL, CREATE VIEW, DROP TABLE
DBMS language that essentially builds empty tables, data dictionary, constraints, etc.
Data manipulation language (DML)
SQL commands such as SELECT, WHERE, INSERT, GROUP BY, UPDATE, DELETE, ROLLBACK. Populating, search, querying data (manipulates end-user data)
Common SQL datatypes
Numeric, Character (CHAR, VARCHAR - not used, VARCHAR2 - use this for variable length SQL strings), Date ('10-Feb-2014')
Example create table code
CREATE TABLE NAME (
P_CODE VARCHAR(10) NOT NULL UNIQUE,
P_INDATE DATE NOT NULL,
PRIMARY KEY(P_CODE), (Unique is redundant)
FOREIGN KEY(V_CODE) REFERENCES VENDOR ON UPDATE CASCADE);
ON UPDATE CASCADE
If one primary key is updated, it will also be updated in all other tables that have the foreign key.
ON DELETE CASCADE
When rows in a primary key are deleted, the corresponding rows in the secondary table (foreign key table) are deleted.
DEFAULT (assigns value to attribute when a new row is added to table)
CHECK (validates data when attribute value is entered)
Insert data into a table
INSERT INTO table_name VALUES(values); or INSERT INTO table_name(columns) VALUES(values);
Values must be inputted in the order of the table variables or (columns)
or use a subquery:
INSERT INTO tablename SELECT columnlist FROM tablename;
Command to save changes from your local copy. Ensures database update integrity.
SELECT columns FROM tablename;
* <- all
UPDATE tablename SET columnname = expression (, columnname = expression) [WHERE conditionlist];
SQL is what kind of language?
set-oriented and non procedural.
Set-oriented means it operates over entire sets of rows and columns at once)
Undoes the changes since last COMMIT command. In Oracle ROLLBACK command undoes changes made by last two UPDATE statements.
DELETE FROM tablename WHERE conditionlist;
If no WHERE, it deletes the whole table
Alternate name given to a column or a table to improve readability
Rule of Precedence
SQL operations are like math 2 + 3 * 2 = 8, unless you use parenthesis
IN / NOT IN
AND / OR
BETWEEN (BETWEEN 0 AND 100)
EXISTS (checks if subquery returns any rows)
WHERE EXISTS (subquery) returns TRUE if the subquery results in any rows being returned. If TRUE, the rest of the main query will run. "For whatever rows where this is true, run this main query"
E.g. looking for vendors we need to re-order from:
SELECT * FROM VENDOR
WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH <= P_MIN);
The SQL command used to make changes to table structure.
ALTER TABLE tablename ADD/MODIFY/DROP (columnname [new datatype()]); it adds or drops column or changes column characteristics (VARCHAR(5) to VARCHAR(10))
-add/remove table constraints
ALTER TABLE PART ADD PRIMARY KEY (PART_CODE);
ALTER TABLE PART ADD FOREIGN KEY (V_CODE) REFERENCES VENDOR;
SQL permits copying contents of tables.
Create an empty table (specify rows, types, PK).
Create another table of data. INSERT INTO table1 (table1 columns)
SELECT (table2 columns FROM table2;
CREATE TABLE table1 AS SELECT * from table2;
DROP TABLE tablename;
*Can drop a table only if it is not the one side of any relationship
ORDER BY column1 ASC/DESC, column2 ASC/DESC..;
SELECT DISTINCT columnlist from table; NULLS go to top of the list (unless oracle-bottom).
Other SQL functions
COUNT (*) counts NULLS
COUNT(columnName) counts the values in the column, MIN, MAX, SUM, AVG
has a HAVING condition associated with it to further filter the group by rows
SELECT P_CODE, AVG(PRICE) FROM PRODUCT GROUP BY P_CODE, ORDER BY P_CODE;
GROUP BY V_CODE HAVING AVG(PRICE) < 10; (where the average price of each vendor is less than 10)
Join on PK/FK, Use an alias in the join like FROM PRODUCT P
INNER JOIN is the default if you don't specify the type when you use the word JOIN
Instead of JOIN, you could also have n-1 join conditions in the WHERE statement (like WHERE C.CUS_CODE = I.CUS_CODE). You could also not use equijoin and do != >, <, etc.
Equijoin vs. Theta join
In a join condition like (WHERE C.CUS_CODE = I.CUS_CODE). You could also not use = (equijoin) and do != >, <, etc. (theta join)
Joining a table with itself (use different aliases). Example: Employee ID column and an Employee manager column.
SELECT E.EMP_NUM, E.EMP_NAME, E.EMP_MGR, M.EMP_NAME
FROM EMP E EMP M
WHERE E.EMP_MGR = M.EMP_NUM
Just multiplication, n*m rows outputted
SELECT * FROM T1, T2 or
SELECT * FROM T1 CROSS JOIN T2
Is SQL declarative or imperative?
declarative. set-oriented. No for loops, if statements. C# actually has both ways.
Types of inner joins
SELECT * FROM T1, T2 WHERE T1.C1 = T2.C1;
SELECT * FROM T1 NATURAL JOIN T2;
SELECT * FROM T1 JOIN T2 USING (C1);
SELECT * FROM T1 JOIN T2 ON (T1.C1 = T2.C1);
LEFT OUTER JOIN
SELECT * FROM T1 LEFT OUTER JOIN T2 ON T1.C1 = T2.C1; //returns all the rows from T1 and only the matching rows from T2.
RIGHT OUTER JOIN
SELECT * FROM T1 RIGHT OUTER JOIN T2 ON T1.C1 = T2.C1; //returns all the rows from T2 and only the matching rows from T1.
FULL OUTER JOIN
SELECT * FROM T1 FULL OUTER JOIN T2 ON T1.C1 = T2.C1; //returns all the rows from T1 and T2, regardless of whether or not they have matching values in the other table
Allows comparison of a single value with a list of values returned by the first subquery. > ALL is like 'greater than the largest of'
E.g. which products do we own in our store, whose value is more than ALL other products' values supplied by vendors in Florida?
P_PRICE > ALL (SELECT P_QOH
P_PRICE FROM PRODUCT WHERE V_CODE...)
Allows comparison of a single value to a list of values and selects only the rows for which the value is greater than or less than any value in the list. Note: M = ANY (6,8,9) is equivalent to M IN (6,8,9).
A subquery that executes once for each row in the outer query, passing it to the inner query.
e.g. show all product sales in which the units sold value is greater than the average units sold value for that product (as opposed to overall for all products)
SELECT INV_NUMBER, P_CODE, LINE_UNITS FROM LINE LS WHERE LS.LINE_UNITS > (SELECT AVG(LINE_UNITS) FROM LINE LA WHERE LA.P_CODE = LS.P_CODE;
Numeric, date and time or string,
conversion functions (date format or currency conversion)
Duplicates are counted once (unless UNION ALL). All students.
INTERSECT - only students in both tables.
Two or more tables that share the same column names and have columns with compatible data types.
MINUS - a-b are students only in a (and not b). b-a are students only in b (and not a)
query EXCEPT query
query MINUS query
alternative: IN and NOT IN
A virtual table produced as a result of a SELECT query. You can name the view and store it and use it later to use in other queries. If you turn off your computer it disappears unless you materialize the view.
CREATE VIEW viewname AS SELECT query
Base tables - tables on which the view is based. Stores in the data dictionary.
monotonically increasing numbers (1000,1001, 1002...)
-has a name, not tied to any table or column
- generate a numeric value that can be assigned to any column in any table
- table attribute with assigned value can be edited and modified
- can be created or deleted at any time
CREATE SEQUENCE name START WITH 20010 NOCACHE;
SELECT * FROM name;
INSERT INTO CUSTOMER VALUES(name.NEXTVAL, 'Connery', 'Sean', NULL, '615', ..); first time it's 20010 then used again it's 20011
.NEXTVAL gives current value then increments .CURRVAL gives current value (if they buy multiple items)
Procedural Language SQL (PL/SQL)
involves extra (augmented) syntax that lets us do looping, branching, variable declaration and function declaration in SQL
Some things that PL/SQL allow:
- blocks of code (or persistent stored module (PSM)) for ONE-time execution, no name (anonymous)
- stored procedures: named procedures (no return values) for repeated calling (not one time)
- stored functions: named functions (with return values) for repeated calling (not one time)
- triggers: callbacks to invoke (callback functions are code that run only demand, when certain conditions are met)
Callbacks to invoke. A procedural SQL code that is automatically invoked by the relational database management system when a data manipulation event occurs. Any change in the table gives a trigger. If someone changes a students GPA, don't run it and notify me.
BEFORE and AFTER. Should it make the change then notify or wait? This timing indicates when the trigger's PL/SQL code executes - in this case, before or after the triggering statement is completed.
The statement that causes the trigger to execute (e.g. row adding, row deletion, modification). (INSERT, UPDATE, or DELETE)
The two types of triggers are statement-level triggers and row-level triggers.
Statement-level trigger: is assumed (default) if you omit the FOR EACH ROW keywords. This type of trigger is executed once, before or after the triggering statement is completed.
Row-level trigger: requires the FOR EACH ROW keywords. This type of trigger is executed once for each row affected by the triggering statement. (in other words, if you update 10 rows, the trigger executes 10 times)
The PL/SQL code enclosed between the BEGIN and END keywords.
block creation example
SET SERVER OUTPUT ON then in the SQL pull after the insert put DBMS_OUTPUT.PUT_LINE('New Vendor Added'); (image)
trigger code example
CREATE OR REPLACE TRIGGER trigger_name [BEFORE/AFTER] [DELETE/INSERT/UPDATE OF column_name] ON table_name [FOR EACH ROW]
You can also:
DROP TRIGGER trigger_name
named procedures (no return values) for repeated calling (not one time)
stored procedure example
CREATE OR REPLACE PROCEDURE proc_name AS BEGIN
To run it:
named functions (with return values) for repeated calling (not one time). You can be invoked from a procedure or trigger.
CREATE FUNCTION func_name (argument IN data-type..) RETURN data-type AS BEGIN
RETURN (value or expression);
To run it:
DECLARE a number; b number;///
YOU MIGHT ALSO LIKE...
SQL interview Questions 2
IS 380 Final
OTHER SETS BY THIS CREATOR
585 Lecture 3