Sql

Front 1

Insert into

Back 1

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

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

Front 2

Update

Back 2

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

Front 3

Like

Back 3

SELECT * FROM Customers
WHERE Country LIKE '%land%';

Front 4

Wildcards

Back 4

%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 bra

Front 5

Wildcards

Back 5

SELECT * FROM Customers
WHERE City LIKE '[a-c]%';

Front 6

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID

Back 6

he INNER JOIN keyword selects all rows from both tables as long as there is a match between the columns. If there are rows in the "Customers" table that do not have matches in "Orders", these customers will NOT be listed.

Front 7

SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID

Back 7

The LEFT JOIN keyword returns all the rows from the left table (Customers), even if there are no matches in the right table (Orders).

Front 8

SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID

Back 8

The RIGHT JOIN keyword returns all the rows from the right table (Employees), even if there are no matches in the left table (Orders).

Front 9

Joins

Back 9

* INNER JOIN: Returns all rows when there is at least one match in BOTH tables
* LEFT JOIN: Return all rows from the left table, and the matched rows from the right table
* RIGHT JOIN: Return all rows from the right table, and the matched rows from the left table
* FULL JOIN: Return all rows when there is a match in ONE of the tables

Front 10

Select into

Back 10

SELECT *
INTO newtable [IN externaldb]
FROM table1;

Front 11

Insert into

Back 11

INSERT INTO table2
SELECT * FROM table1;

Front 12

Constraints

Back 12

* NOT NULL - Indicates that a column cannot store NULL value
* UNIQUE - Ensures that each row for a column must have a unique value
* PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Ensures that a column (or combination of two or more columns) have an unique identity which helps to find a particular record in a table more easily and quickly
* FOREIGN KEY - Ensure the referential integrity of the data in one table to match values in another table
* CHECK - Ensures that the value in a column meets a specific condition
* DEFAULT - Specifies a default value when specified none for this column

Front 13

Index

Back 13

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.

Front 14

IsNull

Back 14

ISNULL(UnitsOnOrder,0)

Front 15

Sql Functions

Back 15

* AVG() - Returns the average value
* COUNT() - Returns the number of rows
* FIRST() - Returns the first value
* LAST() - Returns the last value
* MAX() - Returns the largest value
* MIN() - Returns the smallest value
* SUM() - Returns the sum

Front 16

Sql Scalar Functions

Back 16

* UCASE() - Converts a field to upper case
* LCASE() - Converts a field to lower case
* MID() - Extract characters from a text field
* LEN() - Returns the length of a text field
* ROUND() - Rounds a numeric field to the number of decimals specified
* NOW() - Returns the current system date and time
* FORMAT() - Formats how a field is to be displayed

Front 17

The HAVING Clause

Back 17

The HAVING clause was added to SQL because the WHERE keyword could not be used with aggregate functions.

Front 18

Union vs Union All

Back 18

UNION removes duplicate records (where all columns in the results are the same), UNION ALL does not.

Front 19

Pivot

Back 19

SELECT ,

[first pivoted column] AS ,

[last pivoted column] AS

FROM

PIVOT

(

()

FOR

[]

IN ( [first pivoted column], ... [last pivoted column])

) AS

;

Front 20

Dynamic Sql

Back 20

DECLARE @city varchar(75)
SET @city = 'London'
SELECT * FROM customers WHERE City = @city

Front 21

Dynamic Sql EXEC

Back 21

DECLARE @sqlCommand varchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = '''London'''
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = ' + @city
EXEC (@sqlCommand)

Front 22

Dynamic Sql sp_executesql

Back 22

DECLARE @sqlCommand nvarchar(1000)
DECLARE @columnList varchar(75)
DECLARE @city varchar(75)
SET @columnList = 'CustomerID, ContactName, City'
SET @city = 'London'
SET @sqlCommand = 'SELECT ' + @columnList + ' FROM customers WHERE City = @city'
EXECUTE sp_executesql @sqlCommand, N'@city nvarchar(75)', @city = @city

Front 23

Clustered Index vs Non Clustered Inde

Back 23

Clustered Index

* Only one per table
* Faster to read than non clustered as data is physically stored in index order
* Can be used many times per table
* Quicker for insert and update operations than a clustered index

Both types of index will improve performance when select data with fields that use the index but will slow down update and insert operations.

Because of the slower insert and update clustered indexes should be set on a field that is normally incremental ie Id or Timestamp.

SQL Server will normally only use an index if its selectivity is above 95%.

Front 24

Index

Back 24

An index is used to speed up the performance of queries. It does this by reducing the number of database data pages that have to be visited/scanned.

Front 25

Join

Back 25

Front 26

Given the following tables, representing pilots that can fly planes and planes in a hangar:

create table PilotSkills ( pilot_name char(15) not null, plane_name char(15) not null )

create table Hangar ( plane_name char(15) not null )

Select the names of the pilots who can fly every plane in the hangar.

Back 26

select distinct pilot_name from PilotSkills as ps1 where not exists

( select * from hangar where not exists ( select * from PilotSkills as ps2 where ps1.pilot_name = ps2.pilot_name and ps2.plane_name = hangar.plane_name ) )

Front 27

in vs exist vs join

Back 27

SELECT * FROM tableA WHERE tableA.id IN (SELECT tableB.id FROM tableB WHERE title = 'Analyst');

Front 28

in vs exist vs join

Back 28

SELECT * FROM tableA

WHERE EXISTS

(SELECT 1 FROM tableB

WHERE title = 'Analyst' AND tableA.id = tableB.id);

Front 29

in vs exist vs join

Back 29

SELECT * FROM tableA

JOIN tableB ON tableA.id = tableB.id

WHERE tableB.title = 'Analyst';

Front 30

in vs exist vs join

Back 30

In most cases, EXISTS or JOIN will be much more efficient (and faster) than an IN statement.

Front 31

in vs exist vs join

Back 31

When using an IN combined with a subquery, the database must process the entire subquery first, then process the overall query as a whole, matching up based on the relationship specified for the IN.

With an EXISTS or a JOIN, the database will return true/false while checking the relationship specified. Unless the table in the subquery is very small, EXISTS or JOIN will perform much better than IN.