Notesale: Turn your study into money

Document Preview

Extracts from the notes are below, to see the PDF you'll receive please use the links above

---

ANSI SQL through MySQL
1
...
g
...
);

CREATE TABLE Employees
(EmployeeID INT PRIMARY KEY,
FirstName VARCHAR(50), LastName
VARCHAR(50));

ALTER

Modifies the
structure of an
existing database
object

ALTER TABLE
table_name ADD
column_name datatype;

ALTER TABLE Employees ADD
Email VARCHAR(100);

DROP

Deletes an existing
database object

DROP TABLE
table_name;

DROP TABLE Employees;

TRUNCATE TABLE
table_name;

TRUNCATE TABLE Employees;

Removes all data
TRUNCATE from a table

RENAME

Changes the name
of an existing
database object

ALTER TABLE old_name ALTER TABLE Employees RENAME
RENAME TO new_name; TO Staff;

2
...
doe@example
...
DCL (Data Control Language)
SQL
Statement

Explanation

Syntax

Example

GRANT

Gives specific
privileges to a user

GRANT privilege_name ON
object_name TO user;

GRANT SELECT, INSERT ON
Employees TO 'user'@'localhost';

REVOKE

REVOKE privilege_name
Removes specific
ON object_name FROM
privileges from a user user;

REVOKE INSERT ON
Employees FROM
'user'@'localhost';

4
...
Constraints
Constraint

Explanation

Syntax

Example

Uniquely
identifies each
PRIMARY KEY record in a table

column_name datatype
PRIMARY KEY

EmployeeID INT PRIMARY
KEY

Links two tables
FOREIGN KEY together

FOREIGN KEY
(column_name)
REFERENCES
other_table(column_name)

FOREIGN KEY (DepartmentID)
REFERENCES
Departments(DepartmentID)

column_name datatype
UNIQUE

Email VARCHAR(100)
UNIQUE

UNIQUE

Ensures all
values in a
column are
different

NOT NULL

Ensures a column
cannot have a
column_name datatype NOT
NULL value
NULL

FirstName VARCHAR(50) NOT
NULL

CHECK

Ensures all
values in a
column satisfy a
specific condition CHECK (condition)

CHECK (Age >= 18)

DEFAULT

Sets a default
value for a
column

HireDate DATE DEFAULT
CURRENT_DATE

column_name datatype
DEFAULT default_value

6
...
1 AS
PriceWithTax FROM Products;

Comparison

=, <>, !=, >, <,
>=, <=
Compare two values

SELECT * FROM Orders WHERE
TotalAmount > 1000;

Logical

Combine multiple
AND, OR, NOT conditions

SELECT * FROM Employees WHERE
Department = 'Sales' AND Salary >
50000;

BETWEEN

BETWEEN

Check if a value is within
a range

SELECT * FROM Products WHERE Price
BETWEEN 10 AND 20;

LIKE

LIKE

Pattern matching with
wildcard characters

SELECT * FROM Employees WHERE
LastName LIKE 'S%';

IN

IN

Specify multiple values in SELECT * FROM Orders WHERE Status
a WHERE clause
IN ('Shipped', 'Delivered');

IS NULL

IS NULL

Check for null values

SELECT * FROM Customers WHERE
Phone IS NULL;

7
...
SQL CLAUSES
Clause

Explanation

Syntax

Example
SELECT * FROM Employees

WHERE

Filters records based

SELECT column1, column2 FROM

WHERE Department =

on a condition

table_name WHERE condition;

'Sales';
SELECT Department,

SELECT column1,

COUNT(*) FROM

GROUP

Groups rows that have

aggregate_function(column2) FROM

Employees GROUP BY

BY

the same values

table_name GROUP BY column1;

Department;
SELECT Department,

HAVING

SELECT column1,

AVG(Salary) FROM

aggregate_function(column2) FROM

Employees GROUP BY

Specifies a search

table_name GROUP BY column1 HAVING Department HAVING

condition for a group

condition;

AVG(Salary) > 50000;

SELECT column1, column2 FROM
ORDER
BY

Sorts the result set

table_name ORDER BY column1

SELECT * FROM Employees

[ASC/DESC];

ORDER BY LastName ASC;

Specifies the maximum
LIMIT

SELECT * FROM Products

number of records to

SELECT column1, column2 FROM

ORDER BY Price DESC

return

table_name LIMIT number;

LIMIT 10;
SELECT DISTINCT

Returns only distinct
DISTINCT (different) values

SELECT DISTINCT column1, column2

Department FROM

FROM table_name;

Employees;

9
...
OrderID, c
...
column =

FROM Orders o INNER JOIN Customers c

JOIN

in both tables

ON o
...
CustomerID;

table2
...
EmployeeID,

from the left table,

table2 ON

d
...
column =

LEFT JOIN Departments d ON

JOIN

from the right table

e
...
DepartmentID;

table2
...
OrderID, p
...
column =

FROM Orders o RIGHT JOIN Products p

JOIN

from the left table

ON o
...
ProductID;

table2
...
column =

Employees
...
column;

Departments
...
FirstName, d
...


SUBQUERY

Subquery
Location

Explanation

Example

Used to return a single

SELECT OrderID, (SELECT CustomerName FROM

In SELECT

value to be used in the

Customers WHERE Customers
...
CustomerID) AS CustomerName FROM Orders;

Used to return a virtual

SELECT AVG(OrderTotal) FROM (SELECT CustomerID,

In FROM

table to be used in the

SUM(TotalAmount) AS OrderTotal FROM Orders

clause

outer query

GROUP BY CustomerID) AS CustomerOrderTotals;

Used to return data that will SELECT ProductName FROM Products WHERE
In WHERE

be used in the WHERE

ProductID NOT IN (SELECT DISTINCT ProductID FROM

clause

condition

OrderDetails);

Used with aggregate

SELECT DepartmentID, AVG(Salary) FROM Employees

In HAVING

functions in a GROUP BY

GROUP BY DepartmentID HAVING AVG(Salary) >

clause

clause

(SELECT AVG(Salary) FROM Employees);

11
...


INDICES

Operation

Explanation

Syntax

Example

CREATE INDEX index_name
CREATE

Creates an index

ON table_name (column1,

CREATE INDEX idx_lastname

INDEX

on a table

column2,
...
);

idx_email ON Employees(Email);

Removes an

DROP INDEX index_name

DROP INDEX idx_lastname ON

DROP INDEX index from a table ON table_name;
Displays the

SHOW INDEX FROM

SHOW INDEX indexes on a table table_name;

Employees;
SHOW INDEX FROM
Employees;



---