78 terms

SQL

STUDY
PLAY
What is SQL?
SQL stands for Structured Query Language. SQL lets you access and manipulate databases.
What can SQL do?
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
What is RDBMS and what are some examples?
RDBMS stands for Relational Database Management System. RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access. The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
Are SQL queries case sensitive?
SQL keywords are NOT case sensitive: select is the same as SELECT
What goes at the end of each SQL statement?
Some database systems require a semicolon at the end of each SQL statement. Semicolon is the standard way to separate each SQL statement in database systems that allow more than one SQL statement to be executed in the same call to the server.
What is CRUD?
Create, Read/Retrieve (Select), Update and Delete are the four basic functions of persistent storage.
SELECT Statement
The SELECT statement is used to select data from a database. Syntax:

SELECT column_name, column_name
FROM table_name;

SELECT * FROM table_name;
DISTINCT Statement
In a table, a column may contain many duplicate values; and sometimes you only want to list the different (distinct) values. Syntax:

SELECT DISTINCT column_name,column_name
FROM table_name;
WHERE Clause
The WHERE clause is used to extract only those records that fulfill a specified criterion. Syntax:

SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
AND/OR Operators
The AND & OR operators are used to filter records based on more than one condition. Syntax:

SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');
ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by one or more columns. The ORDER BY keyword sorts the records in ascending order by default. To sort the records in a descending order, you can use the DESC keyword.

SELECT column_name, column_name
FROM table_name
ORDER BY column_name ASC|DESC, column_name ASC|DESC;
INSERT INTO Statement
The INSERT INTO statement is used to insert new records in a table.

INSERT INTO table_name
VALUES (value1,value2,value3,...);

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
UPDATE Statement
The UPDATE statement is used to update existing records in a table.

UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;

Notice the WHERE clause in the SQL UPDATE statement!
The WHERE clause specifies which record or records that should be updated. If you omit the WHERE clause, all records will be updated!
DELETE Statement
The DELETE statement is used to delete rows in a table.

DELETE FROM table_name
WHERE some_column=some_value;

Notice the WHERE clause in the SQL DELETE statement!
The WHERE clause specifies which record or records that should be deleted. If you omit the WHERE clause, all records will be deleted!
SELECT TOP Clause
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. Note: Not all database systems support the SELECT TOP clause.

SELECT TOP number|percent column_name(s)
FROM table_name;
LIKE Operator
The LIKE operator is used to search for a specified pattern in a column.

SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;

SELECT * FROM Customers
WHERE City LIKE 's%';
IN Clause
The IN operator allows you to specify multiple values in a WHERE clause.

SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);

SELECT * FROM Customers
WHERE City IN ('Paris','London');
BETWEEN operator
The BETWEEN operator selects values within a range. The values can be numbers, text, or dates.

SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
Aliases (AS)
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.

SELECT column_name AS alias_name
FROM table_name;

SELECT column_name(s)
FROM table_name AS alias_name;
JOIN
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
INNER JOIN
The INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns in both tables.

SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;

SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
LEFT JOIN
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.

SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
RIGHT JOIN
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.

SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
FULL OUTER JOIN
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.

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
UNION
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.

SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
SELECT INTO
The SELECT INTO statement selects data from one table and inserts it into a new table.

SELECT *
INTO newtable [IN externaldb]
FROM table1;

SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;
INSERT INTO
The INSERT INTO SELECT statement selects data from one table and inserts it into an existing table. Any existing rows in the target table are unaffected.

INSERT INTO table2
SELECT * FROM table1;

INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
CREATE DATABASE
The CREATE DATABASE statement is used to create a database.

CREATE DATABASE dbname;
CREATE TABLE
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.

CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
SQL constraints
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).

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,
....
);
NOT NULL Constraint
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.

CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
UNIQUE Constraint
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.

CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
PRIMARY KEY
The 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.

CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
FOREIGN KEY
A FOREIGN KEY in one table points to a PRIMARY KEY in another table.

CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
)
CHECK Constraint
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.

CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
DEFAULT Constraint
The DEFAULT constraint is used to insert a default value into a column. The default value will be added to all new records, if no other value is specified.

CREATE TABLE Persons (
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
);
INDEX
The CREATE INDEX statement is used to create indexes in tables. Indexes allow the database application to find data fast; without reading the whole table. Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update). So you should only create indexes on columns (and tables) that will be frequently searched against.

CREATE INDEX index_name
ON table_name (column_name)
DROP
Indexes, tables, and databases can easily be deleted/removed with the DROP statement.

DROP INDEX table_name.index_name

DROP DATABASE database_name
ALTER
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table. To add a column in a table, use the following syntax:

ALTER TABLE table_name
ADD column_name datatype

ALTER TABLE table_name
DROP COLUMN column_name
AUTO INCREMENT
Auto-increment allows a unique number to be generated when a new record is inserted into a table. Very often we would like the value of the primary key field to be created automatically every time a new record is inserted.

CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
Views
A view is a virtual table.
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 column_name(s)
FROM table_name
WHERE condition
UDPATE VIEW
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
DROP VIEW
DROP VIEW view_name
SQL Dates
The most difficult part when working with dates is to be sure that the format of the date you are trying to insert, matches the format of the date column in the database. SQL Server comes with the following data types for storing a date or a date/time value in the database:

DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: a unique number
Null Values
NULL values represent missing unknown data. By default, a table column can hold NULL values. If a column in a table is optional, we can insert a new record or update an existing record without adding a value to this column. This means that the field will be saved with a NULL value.

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL

SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL
Data Type: Character
Character string. Fixed-length n

char(32)
Data Type: Variable Character
Character string. Variable length. Maximum length n

varchar(64)
Data Type: Boolean
Stores TRUE or FALSE values

boolean
Data Type: Integer
Integer numerical (no decimal). Precision p

int
Data Type: Decimal
Exact numerical, precision p, scale s. Example: decimal(5,2) is a number that has 3 digits before the decimal and 2 digits after the decimal.

decimal(6,2)
Data Type: Float
Approximate numerical, mantissa precision 16

float
Data Type: Real
Approximate numerical, mantissa precision 7

real
Data Type: Date
Stores year, month, and day values

date
Data Type: Time
Stores hour, minute, and second values

time
Data Type: Timestampe
Stores year, month, day, hour, minute, and second values

timestamp
Data Type: Binary Object
Binary string. Fixed-length n

Syntax:
Blob
Text

Fixed Length Syntax:
Binary (fixed up to 8K)
Varbinary (<8K)
Image (<2GB)
Data Type: Aray
A set-length and ordered collection of elements
SQL Functions
SQL has many built-in functions for performing calculations on data. Aggregate functions and Scalar Functions.
Average Function
The AVG() function returns the average value of a numeric column.

SELECT AVG(column_name) FROM table_name
Count Function
The COUNT() function returns the number of rows that matches a specified criteria. The COUNT(column_name) function returns the number of values (NULL values will not be counted) of the specified column:

SELECT COUNT(column_name) FROM table_name;
First Function
The FIRST() function returns the first value of the selected column. Only in MS Access

SELECT FIRST(column_name) FROM table_name;
Last Function
The LAST() function returns the last value of the selected column. Only in MS Access

SELECT LAST(column_name) FROM table_name;
Max Function
The MAX() function returns the largest value of the selected column.

SELECT MAX(column_name) FROM table_name;
Min Function
The MIN() function returns the smallest value of the selected column.

SELECT MIN(column_name) FROM table_name;
Sum Function
The MIN() function returns the smallest value of the selected column.

SELECT MIN(column_name) FROM table_name;
Group By Function
The GROUP BY statement is used in conjunction with the aggregate functions to group the result-set by one or more columns.

SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
Having Function
The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

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;
Upper Case Function
The UCASE() function converts the value of a field to uppercase.

SELECT UPPER(column_name) FROM table_name;
Lower Case Function
The LCASE() function converts the value of a field to lowercase.

SELECT LOWER(column_name) FROM table_name;
Mid Function
The MID() function is used to extract characters from a text field.

SELECT MID(column_name,start,length) AS some_name FROM table_name;
Length Function
The LEN() function returns the length of the value in a text field.

SELECT LENGTH(column_name) FROM table_name;
Round Function
The ROUND() function is used to round a numeric field to the number of decimals specified. NOTE: Many database systems have adopted the IEEE 754 standard for arithmetic operations, according to which the default rounding behavior is "round half to even." In this scheme, .5 is rounded to the nearest even integer. So, both 11.5 and 12.5 would be rounded to 12.

SELECT ROUND(column_name,decimals) FROM table_name;
Now Function
The NOW() function returns the current system date and time.

SELECT NOW() FROM table_name;
Format Function
The FORMAT() function is used to format how a field is to be displayed.

SELECT FORMAT(column_name,format) FROM table_name;
Subquery
A subquery is a SQL query nested inside a larger query.

SELECT a.studentid, a.name, b.total_marks
FROM student a, marks b
WHERE a.studentid = b.studentid AND b.total_marks >
(SELECT total_marks
FROM marks
WHERE studentid = 'V002');
Intersect
Use this keyword to return only values that are in the first query AND also in the second query.

Same as INNER JOIN
Schema
A schema is a collection of database objects (tables) associated with one particular database username. This username is called the schema owner, or the owner of the related group of objects. You may have one or multiple schemas in a database. Basically, any user who creates an object has just created his or her own schema. So, based on a user's privileges within the database, the user has control over objects that are created, manipulated, and deleted. A schema can consist of a single table and has no limits to the number of objects that it may contain, unless restricted by a specific database implementation.
YOU MIGHT ALSO LIKE...