SQL COMMANDS LATEST STUDY GUIDE

SQL COMMANDS LATEST STUDY GUIDE The SELECT statement is used to select data from a database. The result is stored in a result table, called the result-set. ans: SELECT column_name,column_name FROM table_name; SELECT * FROM table_name; The SELECT DISTINCT statement is used to return only distinct (different) values ans: SELECT DISTINCT column_name,column_name FROM table_name; The WHERE clause is used to extract only those records that fulfill a specified criterion. ans: SELECT column_name,column_name FROM table_name WHERE column_name operator value; he AND operator displays a record if both the first condition AND the second condition are true. The OR operator displays a record if either the first condition OR the second condition is true. ans: SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin'; The ORDER BY keyword is used to sort the result-set. ans: SELECT column_name, column_name FROM table_name ORDER BY column_name ASC|DESC, column_name ASC|DESC; The INSERT INTO statement is used to insert new records in a table. ans: INSERT INTO table_name (column1,column2,column3,...) VALUES (value1,value2,value3,...); The UPDATE statement is used to update records in a table. ans: UPDATE table_name SET column1=value1,column2=value2,... WHERE some_column=some_value; The DELETE statement is used to delete records in a table. ans: DELETE FROM table_name WHERE some_column=some_value; SQL injection is a technique where malicious users can inject SQL commands into an SQL statement, via web page input. Injected SQL commands can alter SQL statement and compromise the security of a web application. ans: txtUserId = getRequestString("UserId"); txtSQL = "SELECT * FROM Users WHERE UserId = " + txtUserId; The SELECT TOP clause is used to specify the number of records to return. The SELECT TOP clause can be very useful on large tables with thousands of records. Returning a large number of records can impact on performance. ans: SELECT TOP number|percent column_name(s) FROM table_name; The LIKE operator is used in a WHERE clause to search for a specified pattern in a column. ans: SELECT column_name(s) FROM table_name WHERE column_name LIKE pattern; % A substitute for zero or more characters _ A substitute for a single character [charlist] Sets and ranges of characters to match [^charlist] or [!charlist] Matches only a character NOT specified within the brackets ans: SQL wildcards The IN operator allows you to specify multiple values in a WHERE clause. ans: SELECT column_name(s) FROM table_name WHERE column_name IN (value1,value2,...); The BETWEEN operator selects values within a range. The values can be numbers, text, or dates. ans: SELECT column_name(s) FROM table_name WHERE column_name BETWEEN value1 AND value2; SQL aliases are used to give a database table, or a column in a table, a temporary name. Basically aliases are created to make column names more readable ans: SELECT column_name AS alias_name FROM table_name; The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables. ans: SELECT column_name(s) FROM table1 INNER JOIN table2 ON n_name=n_name; The LEFT JOIN keyword returns all rows from the left table (table1), with the matching rows in the right table (table2). The result is NULL in the right side when there is no match. ans: SELECT column_name(s) FROM table1 LEFT JOIN table2 ON n_name=n_name; The RIGHT JOIN keyword returns all rows from the right table (table2), with the matching rows in the left table (table1). The result is NULL in the left side when there is no match. ans: SELECT column_name(s) FROM table1 RIGHT JOIN table2 ON n_name=n_name; The FULL OUTER JOIN keyword returns all rows from the left table (table1) and from the right table (table2). The FULL OUTER JOIN keyword combines the result of both LEFT and RIGHT joins. ans: SELECT column_name(s) FROM table1 FULL OUTER JOIN table2 ON n_name=n_name; The UNION operator is used to combine the result-set of two or more SELECT statements. Notice that each SELECT statement within the UNION must have the same number of columns. The columns must also have similar data types. Also, the columns in each SELECT statement must be in the same order. ans: SELECT column_name(s) FROM table1 UNION SELECT column_name(s) FROM table2; With SQL, you can copy information from one table into another. The SELECT INTO statement copies data from one table and inserts it into a new table ans: SELECT * INTO newtable [IN externaldb] FROM table1; With SQL, you can copy information from one table into another. The INSERT INTO SELECT statement copies data from one table and inserts it into an existing table. ans: INSERT INTO table2 SELECT * FROM table1; The CREATE DATABASE statement is used to create a database. ans: CREATE DATABASE dbname; The CREATE TABLE statement is used to create a table in a database. Tables are organized into rows and columns; and each table must have a name. ans: CREATE TABLE table_name ( column_name1 data_type(size), column_name2 data_type(size), column_name3 data_type(size), .... ); SQL constraints are used to specify rules for the data in a table. If there is any violation between the constraint and the data action, the action is aborted by the constraint. Constraints can be specified when the table is created (inside the CREATE TABLE statement) or after the table is created (inside the ALTER TABLE statement). ans: CREATE TABLE table_name ( column_name1 data_type(size) constraint_name, column_name2 data_type(size) constraint_name, column_name3 data_type(size) constraint_name, .... ); The NOT NULL constraint enforces a column to NOT accept NULL values. The NOT NULL constraint enforces a field to always contain a value. This means that you cannot insert a new record, or update a record without adding a value to this field. The following SQL enforces the "P_Id" column and the "LastName" column to not accept NULL values: ans: CREATE TABLE PersonsNotNull ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) The UNIQUE constraint uniquely identifies each record in a database table. The UNIQUE and PRIMARY KEY constraints both provide a guarantee for uniqueness for a column or set of columns. A PRIMARY KEY constraint automatically has a UNIQUE constraint defined on it. Note that you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table. ans: The following SQL creates a UNIQUE constraint on the "P_Id" column when the "Persons" table is created: CREATE TABLE Persons ( P_Id int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255) ) he PRIMARY KEY constraint uniquely identifies each record in a database table. Primary keys must contain UNIQUE values. A primary key column cannot contain NULL values. Most tables should have a primary key, and each table can have only ONE primary key. ans: CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), PRIMARY KEY (P_Id) ) FOREIGN KEY ans: A FOREIGN KEY in one table points to a PRIMARY KEY in another table. Let's illustrate the foreign key with an example. Look at the following two tables: The "Persons" table: The CHECK constraint is used to limit the value range that can be placed in a column. If you define a CHECK constraint on a single column it allows only certain values for this column. If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row. ans: The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0. CREATE TABLE Persons ( P_Id int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Address varchar(255), City varchar(255), CHECK (P_Id0) ) The DROP INDEX statement is used to delete an index in a table ans: DROP INDEX index_name ON table_name e ALTER TABLE statement is used to add, delete, or modify columns in an existing table. ans: ALTER TABLE table_name ADD column_name datatype 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. ans: CREATE VIEW view_name AS SELECT column_name(s) FROM table_name WHERE condition The AVG() function returns the average value of a numeric column. ans: SELECT AVG(column_name) FROM table_name The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column ans: SELECT COUNT(column_name) FROM table_name; The FIRST() function returns the first value of the selected column. ans: SELECT FIRST(column_name) FROM table_name; The LAST() function returns the last value of the selected column. ans: SELECT LAST(column_name) FROM table_name; The MAX() function returns the largest value of the selected column. ans: SELECT MAX(column_name) FROM table_name; The MIN() function returns the smallest value of the selected column. ans: SELECT MIN(column_name) FROM table_name; The SUM() function returns the total sum of a numeric column. ans: SELECT SUM(column_name) FROM table_name; The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns. ans: SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name; The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions. ans: SELECT column_name, aggregate_function(column_name) FROM table_name WHERE column_name operator value GROUP BY column_name HAVING aggregate_function(column_name) operator value; The UCASE() function converts the value of a field to uppercase. ans: SELECT UCASE(column_name) FROM table_name; SELECT UPPER(column_name) FROM table_name; The LCASE() function converts the value of a field to lowercase. ans: SELECT LCASE(column_name) FROM table_name; SELECT LOWER(column_name) FROM table_name; The MID() function is used to extract characters from a text field ans: SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name; he LEN() function returns the length of the value in a text field. ans: SELECT LEN(column_name) FROM table_name; The ROUND() function is used to round a numeric field to the number of decimals specified. ans: SELECT ROUND(column_name,decimals) FROM table_name; he NOW() function returns the current system date and time. ans: SELECT NOW() FROM table_name; he FORMAT() function is used to format how a field is to be displayed. ans: SELECT FORMAT(column_name,format) FROM table_name; How to select a cateogry that does one thing but not another. ans: SELECT maker, type FROM product WHERE type = 'PC' AND type 'Laptop' Unions and Joins: Return the models and prices for all the products (of any type) produced by maker B. ans: SELECT P, price FROM Product, PC WHERE P=PC.model AND maker='B' UNION SELECT P, price FROM Product, Laptop WHERE P=L AND maker='B' UNION SELECT P, price FROM Product, Printer WHERE P=P AND maker='B' CREATE VIEW 'View name' AS "Query" ans: Enter this command before your query and it will save it so you can use it over and over again.