C170_58_Question_MultChoice_OA_StudyGuide___SQL_Commands.

SQL Commands Please take a look at this list and review any that seem unfamiliar: CREATE TABLE statements and data type assignments create table "tablename" ("column1" "data type", "column2" "data type", "column3" "data type"); Here are the most common Data types: char(size) Fixed-length character string. Size is specified in parenthesis. Max 255 bytes. varchar(size) Variable-length character string. Max size is specified in parenthesis. number(size) Number value with a max number of column digits specified in parenthesis. date Date value number(size,d) Number value with a maximum number of digits of "size" total, with a maximum number of "d" digits to the right of the decimal. CREATE TABLE … LIKE Use CREATE TABLE ... LIKE to create an empty table based on the definition of another table, including any column attributes and indexes defined in the original table: CREATE TABLE new_tbl LIKE orig_tbl; CREATE TABLE ... LIKE creates a new table as an empty copy of the original one. It copies the original table structure exactly, so that each column is preserved with all of its attributes. The index structure is copied as well. However, the new table is empty, so to populate it a second statement is needed (such as INSERT INTO ... SELECT). Also, CREATE TABLE ... LIKE cannot create a new table from a subset of the original table's columns, and it cannot use columns from any other table but the original one. To use CREATE TABLE ... LIKE for creating an empty copy of an existing table, write a statement like this: CREATE TABLE new_tbl_name LIKE tbl_name; CREATE TABLE … SELECT To create one table from another, add a SELECT statement at the end of the CREATE TABLE statement: CREATE TABLE new_tbl AS SELECT * FROM orig_tbl; CREATE TABLE ... SELECT creates a new table from the result of an arbitrary SELECT statement. By default, this statement does not copy all column attributes such as AUTO_INCREMENT. Nor does creating a table by selecting data into it automatically copy any indexes from the original table, because result sets are not themselves indexed. On the other hand, CREATE TABLE ... SELECT can both create and populate the new table in a single statement. It also can create a new table using a subset of the original table and include columns from other tables or columns created as the result of expressions. CREATE TABLE ... SELECT also can create new tables that don't contain exactly the same set of columns in an existing table. You can use it to cause a new table to spring into existence on the fly to hold the result of an arbitrary SELECT query. This makes it exceptionally easy to create a table fully populated with the data in which you're interested, ready to be used in further statements. However, the new table can contain strange column names if you're not careful. When you create a table by selecting data into it, the column names are taken from the columns that you are selecting. To create an empty copy of a table and then populate it from the original table, use CREATE TABLE ... LIKE followed by INSERT INTO ... SELECT: CREATE TABLE new_tbl_name LIKE tbl_name; INSERT INTO new_tbl_name SELECT * FROM tbl_name; ALTER TABLE (what is it used for) ALTER TABLE changes the structure of a table. For example, you can add or delete columns, create or destroy indexes, change the type of existing columns, or rename columns or the table itself. You can also change characteristics such as the storage engine used for the table or the table comment. ALTER TABLE tbl_name [alter_option [, alter_option] ...] [partition_options] ALTER TABLE (examples) ALTER TABLE t2 DROP COLUMN c, DROP COLUMN d; DROP TABLE DROP TABLE removes one or more tables. You must have the DROP privilege for each table. DROP [TEMPORARY] TABLE [IF EXISTS] tbl_name [, tbl_name] ... [RESTRICT | CASCADE] CREATE VIEW In SQL, a view is a virtual table based on the result-set of an SQL statement. A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database. You can add SQL functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table. CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition; ALTER VIEW This statement changes the definition of a view, which must exist. The syntax is similar to that for CREATE VIEW see Section 13.1.21, “CREATE VIEW Statement”). This statement requires the CREATE VIEW and DROP privileges for the view, and some privilege for each column referred to in the SELECT statement. ALTER VIEW is permitted only to the definer or users with the SUPER privilege. ALTER [ALGORITHM = {UNDEFINED | MERGE | TEMPTABLE}] [DEFINER = user] [SQL SECURITY { DEFINER | INVOKER }] VIEW view_name [(column_list)] AS select_statement [WITH [CASCADED | LOCAL] CHECK OPTION] DROP VIEW DROP VIEW removes one or more views. You must have the DROP privilege for each view. DROP VIEW [IF EXISTS] view_name [, view_name] ... [RESTRICT | CASCADE] CREATE INDEX/DROP INDEX The CREATE INDEX statement is used to create indexes in tables. CREATE INDEX index_name ON table_name (column1, column2, ...); The DROP INDEX statement is used to delete an index in a table. MS Access: DROP INDEX index_name ON table_name; SQL Server: DROP INDEX table__name; DB2/Oracle: DROP INDEX index_name; MySQL: ALTER TABLE table_name DROP INDEX index_name; INSERT INTO – 2 methods The INSERT INTO statement is used to insert new records in a table. It is possible to write the INSERT INTO statement in two ways. The first way specifies both the column names and the values to be inserted: INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...); It is also possible to only insert data in specific columns. The following SQL statement will insert a new record, but only insert data in the "CustomerName", "City", and "Country" columns (CustomerID will be updated automatically): INSERT INTO Customers (CustomerName, City, Country) VALUES ('Cardinal', 'Stavanger', 'Norway'); M ulti-row INSERT statements To insert more than one record at once, we can do this, with each set of field values separated by a comma: (BEST) INSERT INTO example (example_id, name, value, other_value) VALUES (100, 'Name 1', 'Value 1', 'Other 1'), (101, 'Name 2', 'Value 2', 'Other 2'), (102, 'Name 3', 'Value 3', 'Other 3'), (103, 'Name 4', 'Value 4', 'Other 4'); You could also omit the fieldnames like this: INSERT INTO example VALUES (100, 'Name 1', 'Value 1', 'Other 1'), (101, 'Name 2', 'Value 2', 'Other 2'), (102, 'Name 3', 'Value 3', 'Other 3'), (103, 'Name 4', 'Value 4', 'Other 4'); INSERT INTO SELECT The INSERT INTO SELECT statement copies data from one table and inserts it into another table.  INSERT INTO SELECT requires that data types in source and target tables match  The existing records in the target table are unaffected INSERT INTO table2 SELECT * FROM table1 WHERE condition; UPDATE The UPDATE statement is used to modify the existing records in a table. UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition; DELETE The DELETE statement is used to delete existing records in a table. DELETE FROM table_name WHERE condition; SELECT The SELECT statement is used to select data from a database. The data returned is stored in a result table, called the result-set. SELECT column1, column2, ... FROM table_name; SELECT DISTINCT The SELECT DISTINCT statement is used to return only distinct (different) values. Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values. SELECT DISTINCT column1, column2, ... FROM table_name; WHERE The WHERE clause is used to filter records. The WHERE clause is used to extract only those records that fulfill a specified condition. SELECT column1, column2, ... FROM table_name WHERE condition; ORDER BY The ORDER BY keyword is used to sort the result-set in ascending or descending order. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the DESC keyword. SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC; LEFT JOIN The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match. SELECT column_name(s) FROM table1 LEFT JOIN table2 ON n_name = n_name; RIGHT JOIN The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match. SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON n_name = n_name; FULL JOIN The FULL OUTER JOIN keyword returns all records when there is a match in left (table1) or right (table2) table records. Note: FULL OUTER JOIN can potentially return very large result-sets! Tip: FULL OUTER JOIN and FULL JOIN are the same. SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON n_name = n_name WHERE condition; COUNT The COUNT() function returns the number of rows that matches a specified criterion. SELECT COUNT(column_name) FROM table_name WHERE condition; AVG The AVG() function returns the average value of a numeric column. SELECT AVG(column_name) FROM table_name WHERE condition; SUM The SUM() function returns the total sum of a numeric column. SELECT SUM(column_name) FROM table_name WHERE condition; Difference between COUNT(column_name) and COUNT(*) COUNT(*) – Returns the total number of records in a table (Including NULL valued records). COUNT(Column Name) – Returns the total number of Non-NULL records. It means that, it ignores counting NULL valued records in that particular column. GROUP BY The GROUP BY statement groups rows that have the same values into summary rows, like "find the number of customers in each country". The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns. SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) ORDER BY column_name(s); Subquery - Difference between row subquery and table subquery A subquery is a SQL query nested inside a larger query.  A subquery may occur in : o - A SELECT clause o - A FROM clause o - A WHERE clause  The subquery can be nested inside a SELECT, INSERT, UPDATE, or DELETE statement or inside another subquery.  A subquery is usually added within the WHERE Clause of another SQL SELECT statement.  You can use the comparison operators, such as , , or =. The comparison operator can also be a multiplerow operator, such as IN, ANY, or ALL