SQL stands for Structured Query Language is used in programming and designed for managing data held in relation database management system or stream process in relational data steam management system. SQL queries is very famous for API testing and back-end database testing.
Here is a real time queries used for SQL database.
VERY USEFUL SQL QUERIES
Here is a real time queries used for SQL database.
- SQL (Structured Query Language)
-- a programming language specifically designed for working with data base
-- allows you to write queries that the computer can execute and then provide database insights in return
-- Oracle, MariaDB, SQLite, MySQL, Microsoft SQLServer, PostgressSQL
-- MySQL is reliable, mature, and open source (free)
-- entity: the smallest unit that can contain a meaningful set of data
-- keyword = reserved word: can not be used when naming object
-- SELECT statement: used to retrieve data from database objects, like tables
-- INSERT statement: used to insert data into tables
-- TRUNCATE allows us to remove all the records contained in a table
-- DELETE, you can specify precisely what you would like to be removed
-- primary key: a column whose value exists and is unique for every record in a table is called a primary key
-- each table can have one and only one primary key
-- primary keys are the unique identifiers of a table
-- primary keys can not contain null values but unique key can have null values
-- primary key is always underlined
-- ; functions as a statement terminator, when your code contain more than a single statement, it is indispensable
MySQL
-- SQL (Structured Query Language)
-- a programming language specifically designed for working with data base
-- allows you to write queries that the computer can execute and then provide database insights in return
-- Oracle, MariaDB, SQLite, MySQL, Microsoft SQLServer, PostgressSQL
-- MySQL is reliable, mature, and open source (free)
-- entity: the smallest unit that can contain a meaningful set of data
-- keyword = reserved word: can not be used when naming object
-- SELECT statement: used to retrieve data from database objects, like tables
-- INSERT statement: used to insert data into tables
-- TRUNCATE allows us to remove all the records contained in a table
-- DELETE, you can specify precisely what you would like to be removed
-- primary key: a column whose value exists and is unique for every record in a table is called a primary key
-- each table can have one and onl one primary key
-- primary keys are the unique identifiers of a table
-- primary keys can not contain null values but unique key can have null values
-- primary key is always underlined
-- ; functions as a statement terminator, when your code contain more than a single statement, it is indispensable
CREATE DATABASE IF NOT EXISTS Sales;
-- IF NOT EXISTS is optional but recommended
USE sales;
DROP TABLE sales;
CREATE TABLE Sales
(
purchase_number INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
date_of_purchase DATE NOT NULL,
customer_id INT,
item_code VARCHAR(10) NOT NULL
);
CREATE TABLE customers
(
customer_id INT,
first_name VARCHAR(255),
last_name VARCHAR(255),
email_address VARCHAR(255),
number_of_complaints int,
UNIQUE KEY (email_address)
);
-- set a default database
SELECT *
FROM sales;
-- call a table from a certain database explicitly
SELECT *
FROM sales.sales;
-- to drop the table
DROP TABLE sales;
-- foreign key: points to a column of another table and thus, links the two tables
-- parent table = referenced table and child table = referencing table
-- ON DELETE CASCADE: If a specific value from the parent table's primary key has been deleted, all the record from the child
-- table referring to this value will also be removed.
-- unique key: used whenever you would like to specify that you don't want to see duplicate data in a given field
-- ensure that all values in a column are different
USE employees;
SELECT
*
FROM
employees;
-- * a wildcard character, means all and everything
SELECT
*
FROM
departments;
-- WHERE clause: allows us to set a condition upon which we will specify what part of the data we want to retrieve from the database
SELECT
*
FROM
employees
WHERE
first_name = 'Denis' AND gender = 'M';
-- = equal operator
SELECT
*
FROM
employees
WHERE
first_name = 'parto'
OR first_name = 'bernie';
SELECT
*
FROM
employees
WHERE
first_name = 'Cathie'
OR first_name = 'Mark'
OR first_name = 'Nathan';
-- use IN clause for the same result
SELECT
*
FROM
employees
WHERE
first_name IN ('Cathie' , 'Mark', 'Nathan');
-- use NOT IN clause
SELECT
*
FROM
employees
WHERE
first_name NOT IN ('Cathie' , 'Mark', 'Nathan');
-- Extract all records from the 'employees' table aside from those with employeees named John, Mark or Jacob.
SELECT
*
FROM
employees
WHERE
first_name NOT IN ('John' , 'Mark', 'Jacob');
-- to get the desired pattern use LIKE clause
SELECT
*
FROM
employees
WHERE
first_name LIKE ('ar%');
-- 'ar%' represents any character after 'ar'
-- % is a substitute for a sequence of characters
SELECT
*
FROM
employees
WHERE
first_name LIKE ('%ar');
-- '%ar' represents any character before ar
SELECT
*
FROM
employees
WHERE
first_name LIKE ('%mar%');
-- _ helps you match a single character
-- Retrieve a list with all employees who have been hired in the year 2000.
SELECT
*
FROM
employees
WHERE
hire_date LIKE ('%2000%');
-- Retrieve a list with all employees whose employee number is written with 5 characters, and starts with '1000'.
SELECT
*
FROM
employees
WHERE
emp_no LIKE ('1000_');
-- Extract all individuals from the employees table whose first name contains "Jack".
SELECT
*
FROM
employees
WHERE
first_name LIKE ('%JACK%');
-- Extract all individuals from the employees table whose first name does not contain "Jack".
SELECT
*
FROM
employees
WHERE
first_name NOT LIKE ('%JACK%');
-- Retrieve all employees who have beeen hired between 1990-01-01 and 2000-01-01
SELECT
*
FROM
employees
WHERE
hire_date BETWEEN '1990-01-01' AND '2000-01-01';
-- WHERE hire_date NOT BETWEEN '1990-01-01' AND '2000-01-01';
-- meaning before '1990-01-01' and after '2000-01-01'
-- Select all the information from the 'salaries' table regarding contracts from 66,000 and 70,000 per year
SELECT
*
FROM
salaries
WHERE
salary BETWEEN 66000 AND 70000;
-- Retrieve a list with all individuals whose employee number is not between '10004' and '10012'
SELECT
*
FROM
salaries
WHERE
salary NOT BETWEEN '10004' AND '10012';
-- Use NULL clause
SELECT
*
FROM
employees
WHERE
first_name IS NOT NULL;
-- Use of 'DISTINCT' clause
SELECT DISTINCT
gender
FROM
employees;
-- aggregate functions: are applied on multiple rows of a single column of a table and return an output of a single value
-- COUNT(): counts the number of non-null records in a field
-- SUM(): sums all the non-null values in a column
-- MIN(): returns the minimum value from the entire list
-- MAX(): returns the maximum value from the entire list
-- AVG(): calculates the average all all non-null values from a certain column of a table
-- How many employees are registered in our database?
SELECT
COUNT(DISTINCT emp_no)
FROM
employees;
-- How many annual contracts with a value higher than or equal to $100,000 have been registered in the salaries table?
SELECT
COUNT(salary)
FROM
salaries
WHERE
salary >= 100000;
-- How many managers do we have in the 'employees' database?
SELECT
COUNT(*)
FROM
dept_manager;
-- Ascending and Descending order
SELECT
*
FROM
employees
ORDER BY first_name DESC;
-- GROUP BY
-- SQL (Structured Query Language)
-- a programming language specifically designed for working with data base
-- allows you to write queries that the computer can execute and then provide database insights in return
-- Oracle, MariaDB, SQLite, MySQL, Microsoft SQLServer, PostgressSQL
-- MySQL is reliable, mature, and open source (free)
-- entity: the smallest unit that can contain a meaningful set of data
-- keyword = reserved word: can not be used when naming object
-- SELECT statement: used to retrieve data from database objects, like tables
-- INSERT statement: used to insert data into tables
-- TRUNCATE allows us to remove all the records contained in a table
-- DELETE, you can specify precisely what you would like to be removed
-- primary key: a column whose value exists and is unique for every record in a table is called a primary key
-- each table can have one and onl one primary key
-- primary keys are the unique identifiers of a table
-- primary keys can not contain null values but unique key can have null values
-- primary key is always underlined
-- ; functions as a statement terminator, when your code contain more than a single statement, it is indispensable
CREATE DATABASE IF NOT EXISTS Sales;
-- IF NOT EXISTS is optional but recommended
USE sales;
USE employees;
DROP TABLE sales;
CREATE TABLE Sales
(
purchase_number INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
date_of_purchase DATE NOT NULL,
customer_id INT,
item_code VARCHAR(10) NOT NULL
);
CREATE TABLE customers
(
customer_id INT,
first_name VARCHAR(255),
last_name VARCHAR(255),
email_address VARCHAR(255),
number_of_complaints int,
UNIQUE KEY (email_address)
);
-- set a default database
SELECT *
FROM sales;
-- call a table from a certain database explicitly
SELECT *
FROM sales.sales;
-- to drop the table
DROP TABLE sales;
-- foreign key: points to a column of another table and thus, links the two tables
-- parent table = referenced table and child table = referencing table
-- ON DELETE CASCADE: If a specific value from the parent table's primary key has been deleted, all the record from the child
-- table referring to this value will also be removed.
-- unique key: used whenever you would like to specify that you don't want to see duplicate data in a given field
-- ensure that all values in a column are different
USE employees;
SELECT
*
FROM
employees;
-- * a wildcard character, means all and everything
SELECT
*
FROM
departments;
-- WHERE clause: allows us to set a condition upon which we will specify what part of the data we want to retrieve from the database
SELECT
*
FROM
employees
WHERE
first_name = 'Denis' AND gender = 'M';
-- = equal operator
SELECT
*
FROM
employees
WHERE
first_name = 'parto'
OR first_name = 'bernie';
SELECT
*
FROM
employees
WHERE
first_name = 'Cathie'
OR first_name = 'Mark'
OR first_name = 'Nathan';
-- use IN clause for the same result
SELECT
*
FROM
employees
WHERE
first_name IN ('Cathie' , 'Mark', 'Nathan');
-- use NOT IN clause
SELECT
*
FROM
employees
WHERE
first_name NOT IN ('Cathie' , 'Mark', 'Nathan');
-- Extract all records from the 'employees' table aside from those with employeees named John, Mark or Jacob.
SELECT
*
FROM
employees
WHERE
first_name NOT IN ('John' , 'Mark', 'Jacob');
-- to get the desired pattern use LIKE clause
SELECT
*
FROM
employees
WHERE
first_name LIKE ('ar%');
-- 'ar%' represents any character after 'ar'
-- % is a substitute for a sequence of characters
SELECT
*
FROM
employees
WHERE
first_name LIKE ('%ar');
-- '%ar' represents any character before ar
SELECT
*
FROM
employees
WHERE
first_name LIKE ('%mar%');
-- _ helps you match a single character
-- Retrieve a list with all employees who have been hired in the year 2000.
SELECT
*
FROM
employees
WHERE
hire_date LIKE ('%2000%');
-- Retrieve a list with all employees whose employee number is written with 5 characters, and starts with '1000'.
SELECT
*
FROM
employees
WHERE
emp_no LIKE ('1000_');
-- Extract all individuals from the employees table whose first name contains "Jack".
SELECT
*
FROM
employees
WHERE
first_name LIKE ('%JACK%');
-- Extract all individuals from the employees table whose first name does not contain "Jack".
SELECT
*
FROM
employees
WHERE
first_name NOT LIKE ('%JACK%');
-- Retrieve all employees who have beeen hired between 1990-01-01 and 2000-01-01
SELECT
*
FROM
employees
WHERE
hire_date BETWEEN '1990-01-01' AND '2000-01-01';
-- WHERE hire_date NOT BETWEEN '1990-01-01' AND '2000-01-01';
-- meaning before '1990-01-01' and after '2000-01-01'
-- Select all the information from the 'salaries' table regarding contracts from 66,000 and 70,000 per year
SELECT
*
FROM
salaries
WHERE
salary BETWEEN 66000 AND 70000;
-- Retrieve a list with all individuals whose employee number is not between '10004' and '10012'
SELECT
*
FROM
salaries
WHERE
salary NOT BETWEEN '10004' AND '10012';
-- Use NULL clause
SELECT
*
FROM
employees
WHERE
first_name IS NOT NULL;
-- Use of 'DISTINCT' clause
SELECT DISTINCT
gender
FROM
employees;
-- aggregate functions: are applied on multiple rows of a single column of a table and return an output of a single value
-- COUNT(): counts the number of non-null records in a field
-- SUM(): sums all the non-null values in a column
-- MIN(): returns the minimum value from the entire list
-- MAX(): returns the maximum value from the entire list
-- AVG(): calculates the average all all non-null values from a certain column of a table
-- How many employees are registered in our database?
SELECT
COUNT(DISTINCT emp_no)
FROM
employees;
-- How many annual contracts with a value higher than or equal to $100,000 have been registered in the salaries table?
SELECT
COUNT(salary)
FROM
salaries
WHERE
salary >= 100000;
-- How many managers do we have in the 'employees' database?
SELECT
COUNT(*)
FROM
dept_manager;
-- Ascending and Descending order
SELECT
*
FROM
employees
ORDER BY first_name DESC;
-- GROUP BY
SELECT
first_name, COUNT(first_name) AS names_count
FROM
employees
GROUP BY first_name
ORDER BY first_name DESC;
-- Write a query that obtains two columns. The first column must contain annual salaries higher than 80,000 dollars. The
-- second column, renamed to 'emps_with_same_salary', must show the number of employees contracted to that salary. Lastly
-- sort the output by the first column
SELECT
salary, COUNT(emp_no) AS emps_with_same_salary
FROM
salaries
WHERE salary > 80000
GROUP BY salary
ORDER BY salary;
-- Use of HAVING clause
-- Having is like WHERE but applied to the GROUP BY
-- After HAVING, you can have a condition with an aggregate function
-- while WHERE can not use aggregate functions within its condition
SELECT
first_name, COUNT(first_name) AS names_count
FROM
employees
GROUP BY first_name
HAVING COUNT(first_name) > 250
ORDER BY first_name;
-- Select all employees whose averge salary is higher than $120,000.
SELECT
emp_no, AVG(salary)
FROM
salaries
GROUP BY emp_no
HAVING AVG(salary) > 120000
ORDER BY emp_no;
-- Extract a list of all names that are encountered less than 200 times. Let the data refer to people hired after the
-- 1st of January 1999.
SELECT
first_name, COUNT(first_name) AS names_count
FROM
employees
WHERE
hire_date > '1999-01-01'
GROUP BY first_name
HAVING COUNT(first_name)
ORDER BY first_name DESC;
-- Select the employee numbers of all individuals who have signed more than 1 contract after the 1st of January 2000.
SELECT
emp_no
FROM
dept_emp
WHERE
from_date > '2000-01-01'
GROUP BY emp_no
HAVING COUNT(from_date) > 1
ORDER BY emp_no;
-- Use of LIMIT clause
-- Please show me the employee numbers of the 10 highest paid employees in the database
SELECT
*
FROM
salaries
ORDER BY salary DESC
LIMIT 10;
-- Select the first 100 rows from the dept_emp table
SELECT
*
FROM
dept_emp
ORDER BY emp_no asc
LIMIT 100;
-- INSERT statement
INSERT INTO employees
(emp_no, birth_date, first_name, last_name, gender, hire_date)
VALUES('999902', '1986-04-25', 'Mahesh', 'Smith', 'M', '2011-01-01');
-- UPDATE Statement: update the values of existing table
SELECT
*
FROM
employees
WHERE
emp_no = 999901;
UPDATE employees
SET
first_name = 'Ram',
last_name = 'Neupane',
birth_date = '1986-01-01',
gender = 'M'
WHERE
emp_no = 999901;
-- DELETE statement
DELETE FROM employees
WHERE
emp_no = 999901;
-- Remove the department number 10 record from the departments table
DELETE FROM departments
WHERE
dept_no = 'd010';
-- use of aggregate functions
SELECT
MIN(salary)
FROM
salaries;
SELECT
AVG(salary)
FROM
salaries
WHERE
from_date > '1997-01-01';
-- ROUND(#)
SELECT
ROUND(AVG(salary))
FROM
salaries;
SELECT
ROUND(AVG(salary), 2)
FROM
salaries;
-- Round the average amount of money spent on salaries fro all contracts that started after 1st of January 1997 to a precision of cents.
SELECT
ROUND(AVG(salary), 2)
FROM
salaries
WHERE
from_date > '1997-01-01';
use employees;
-- JOIN clause: allows us to construct a relationship between objects
-- We must find a related column from the two tables that contains the same type of data
-- Create and fill in the ‘dept_manager_dup’ table, using the following code:
CREATE TABLE dept_manager_dup (
emp_no INT(11) NOT NULL,
dept_no CHAR(4) NULL,
from_date DATE NOT NULL,
to_date DATE NULL
);
INSERT INTO dept_manager_dup(emp_no, from_date)
VALUES (999904, '2017-01-01'),
(999905, '2017-01-01'),
(999906, '2017-01-01');
SELECT
m.dept_no, m.emp_no, d.dept_name
FROM
dept_manager_dup m
INNER JOIN
departments_dup d ON m.dept_no = m.dept_no
ORDER BY m.dept_no;
SELECT
*
FROM
orders
JOIN
customers ON orders.customer_id = customers.customer_id;
-- Join tables accross multiple databases
SELECT
*
FROM
order_items oi
JOIN
sql_inventory.products p ON oi.product_id = p.product_id;
-- Use of SELFJOIN---
USE sql_hr;
SELECT
e.employee_id, e.first_name, m.first_name AS manager
FROM
employees e
JOIN
employees m ON e.reports_to = m.employee_id;
-- JOIN Multiple tables (more than two tables)
USE sql_store;
SELECT
o.order_id,
o.order_date,
c.first_name,
c.last_name,
os.name AS status
FROM
orders o
JOIN
customers c ON o.customer_id = c.customer_id
JOIN
order_statuses os ON o.status = os.order_status_id;
-- Compound JOIN condition
SELECT
*
FROM
order_items oi
JOIN
order_item_notes oin ON oi.order_id = oin.order_id
AND oi.product_id = oin.product_id;
-- Implicit Join Syntax
SELECT
*
FROM
orders o,
customers c
WHERE
o.customer_id = c.customer_id;
SELECT
c.customer_id, c.first_name, o.order_id
FROM
orders o
RIGHT JOIN
customers c ON c.customer_id = o.customer_id
ORDER BY c.customer_id;
-- USING CLAUSE ( only if column names across differnt are same)
-- Do a cross join between shippers and products
-- using the implicit syntax
-- and then using the explicit syntax
SELECT
sh.name AS shipper, p.name AS product
FROM
shippers sh,
products p
ORDER BY sh.name;
-- which is equivalent to the following
SELECT
sh.name AS shipper, p.name AS product
FROM
shipper sh
CROSS JOIN
products p
ORDER BY sh.name;
-- UNION
SELECT
customer_id, first_name, points, 'Bronze' AS type
FROM
customers
WHERE
points < 2000
UNION SELECT
customer_id, first_name, points, 'Silver' AS type
FROM
customers
WHERE
points BETWEEN 2000 AND 3000
UNION SELECT
customer_id, first_name, points, 'Gold' AS type
FROM
customers
WHERE
points > 3000
ORDER BY first_name;
-- Use of column Attributes: how to insert and delete data
-- inserting a single row
INSERT INTO customers (
first_name,
last_name,
birth_date,
address,
city,
state)
VALUES (
'John',
'Smith',
'1990-01-01',
'address',
'city',
'CA'
);
-- How to insert multiple rows
INSERT INTO shippers (name)
VALUES ('Shipper1'),
('Shipper2'),
('Shipper3');
-- Write a statement to insert three rows in the product table
INSERT INTO products (name, quantity_in_stock, unit_price)
VALUES ('product1', 10, 1.95),
('product2', 11, 1.95),
('product3', 12, 1.95);
-- Example showing JOIN and WHERE
SELECT
e.emp_no, e.first_name, e.last_name, s.salary
FROM
employees e
JOIN
salaries s ON e.emp_no = s.emp_no
WHERE
s.salary > 145000;
-- Select the first and last name, the hire date, and the job title of all employees whose first name is “Margareta” and
-- have the last name “Markovitch”.
SELECT
e.first_name, e.last_name, e.hire_date, t.title
FROM
employees e
JOIN
titles t ON e.emp_no = t.emp_no
WHERE
first_name = 'Margareta'
AND last_name = 'markovitch';
-- CROSS JOIN: this will take the values from a certain table and connect them with all the values
-- from the tables we want to join it with.
-- CROSS JOIN: connects all the values, not just those matched
-- CROSS JOIN: particularly useful when the tables in a databse are not well connected
-- INNER JOIN: typically connects only the matching values
-- Use a CROSS JOIN to return a list with all possible combinations between managers
-- from the dept_manager table and department number 9.
SELECT
dm.*, d.*
FROM
departments d
CROSS JOIN
dept_manager dm
WHERE
d.dept_no = 'd009'
ORDER BY d.dept_name;
-- Return a list with the first 10 employees with all the departments they can be assigned to.
-- Hint: Don’t use LIMIT; use a WHERE clause.
SELECT
e.*, e.*
FROM
employees e
CROSS JOIN
departments d
WHERE
e.emp_no < 10011
ORDER BY e.emp_no , d.dept_name;
-- showing average salary of men and women
SELECT
e.gender, AVG(salary) AS average_salary
FROM
employees e
JOIN
salaries s ON e.emp_no = s.emp_no
GROUP BY gender;
-- connectiong more than one table
SELECT
e.first_name,
e.last_name,
e.hire_date,
m.from_date,
d.dept_name
FROM
employees e
JOIN
dept_manager m ON e.emp_no = m.emp_no
JOIN
departments d ON m.dept_no = d.dept_no;
-- Select all managers’ first and last name, hire date, job title, start date, and department name.
SELECT
e.first_name,
e.last_name,
e.hire_date,
dm.from_date,
t.title,
d.dept_name
FROM
employees e
JOIN
dept_manager dm ON e.emp_no = dm.emp_no
JOIN
departments d ON dm.dept_no = d.dept_no
JOIN
titles t ON e.emp_no = t.emp_no
WHERE
t.title = 'manager'
ORDER BY e.emp_no;
--
SELECT
d.dept_name, AVG(salary) AS average_salary
FROM
departments d
JOIN
dept_manager dm ON d.dept_no = dm.dept_no
JOIN
salaries s ON dm.emp_no = s.emp_no
GROUP BY dept_name
having average_salary > 60000
ORDER BY average_salary DESC;
-- How many male and how many female managers do we have in the ‘employees’ database?
SELECT
e.gender, COUNT(dm.emp_no)
FROM
employees e
JOIN
dept_manager dm ON e.emp_no = dm.emp_no
GROUP BY gender;
-- UNION vs UNION ALL
-- UNION displays only the distinct values and UNION ALL displays duplicates as well
-- Subqueries
-- Select the first and last anme from the 'Employees' table for the same
-- employee numbers that can be found in the 'Department manager' table
SELECT
e.first_name, e.last_name
FROM
employees e
WHERE
e.emp_no IN (SELECT
dm.emp_no
FROM
dept_manager dm);
-- You can have a lot more than one subquery in your outer query
-- it is possible to nest inner queries within other inner queries
-- the inner most query execute first and then each subsequent query, and outermost query at last
-- Extract the information about all department managers who were hired between the 1st of January 1990 and the 1st of January 1995.
SELECT
*
FROM
dept_manager
WHERE
emp_no IN (SELECT
emp_no
FROM
employees
WHERE
hire_date BETWEEN '1990-01-01' AND '1995-01-01');
-- Use of EXIST and NOT EXIST clause
SELECT
e.first_name, e.last_name
FROM
employees e
WHERE
EXISTS( SELECT
*
FROM
dept_manager dm
WHERE
dm.emp_no = e.emp_no);
-- Select the entire information for all employees whose job title is “Assistant Engineer”.
SELECT
*
FROM
employees e
WHERE
EXISTS( SELECT
*
FROM
titles t
WHERE
e.emp_no = t.emp_no
AND title = 'Assistant Engineer');
-- Assign employee number 110022 as a manager to all employees from 10001 to 10020, and employee nuber 110039
-- as a manager to all employees from 10021 to 10040. (VERY IMPORTANT)
SELECT
A.*
FROM
(SELECT
e.emp_no AS employee_ID,
de.dept_no AS department_code,
(SELECT
emp_no
FROM
dept_manager
WHERE
emp_no = '110022') AS manager_ID
FROM
employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE
e.emp_no <= 10020
GROUP BY e.emp_no
ORDER BY e.emp_no) AS A
UNION
SELECT
B.*
FROM
(SELECT
e.emp_no AS employee_ID,
de.dept_no AS department_code,
(SELECT
emp_no
FROM
dept_manager
WHERE
emp_no = '110039') AS manager_ID
FROM
employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE
e.emp_no > 10020
GROUP BY e.emp_no
ORDER BY e.emp_no
LIMIT 20) AS B;
-- Starting your code with “DROP TABLE”, create a table called “emp_manager”
-- (emp_no – integer of 11, not null; dept_no – CHAR of 4, null; manager_no – integer of 11, not null).
drop table if exists emp_manager;
create table emp_manager(
emp_no INT(11) NOT NULL,
dept_no CHAR(4) NULL,
manager_no INT(11) NOT NULL
);
-- Fill emp_manager with data about employees, the number of the department they are working in, and their managers.
INSERT INTO emp_manager
SELECT
u.*
FROM
(SELECT
a.*
FROM
(SELECT
e.emp_no AS employee_ID,
MIN(de.dept_no) AS department_code,
(SELECT
emp_no
FROM
dept_manager
WHERE
emp_no = 110022) AS manager_ID
FROM
employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE
e.emp_no <= 10020
GROUP BY e.emp_no
ORDER BY e.emp_no) AS a UNION SELECT
b.*
FROM
(SELECT
e.emp_no AS employee_ID,
MIN(de.dept_no) AS department_code,
(SELECT
emp_no
FROM
dept_manager
WHERE
emp_no = 110039) AS manager_ID
FROM
employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE
e.emp_no > 10020
GROUP BY e.emp_no
ORDER BY e.emp_no
LIMIT 20) AS b UNION SELECT
c.*
FROM
(SELECT
e.emp_no AS employee_ID,
MIN(de.dept_no) AS department_code,
(SELECT
emp_no
FROM
dept_manager
WHERE
emp_no = 110039) AS manager_ID
FROM
employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE
e.emp_no = 110022
GROUP BY e.emp_no) AS c UNION SELECT
d.*
FROM
(SELECT
e.emp_no AS employee_ID,
MIN(de.dept_no) AS department_code,
(SELECT
emp_no
FROM
dept_manager
WHERE
emp_no = 110022) AS manager_ID
FROM
employees e
JOIN dept_emp de ON e.emp_no = de.emp_no
WHERE
e.emp_no = 110039
GROUP BY e.emp_no) AS d) as u;
-- self join when a table must join itself
-- if you would like to combine certain rows of a table with other rows of the same table, you need a self join
SELECT
*
FROM
emp_manager
ORDER BY emp_manager.emp_no;
SELECT
e1.*
FROM
emp_manager e1
JOIN
emp_manager e2 ON e1.emp_no = e2.manager_no;
#The CREATE DATABASE statement is used to create a new SQL database.
CREATE DATABASE databasename;
#The DROP DATABASE statement is used to drop an existing SQL database.
DROP DATABASE databasename;
#The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database.
BACKUP DATABASE testDB
TO DISK = 'D:\backups\testDB.bak'
WITH DIFFERENTIAL;
#The CREATE TABLE statement is used to create a new table in a database.
CREATE TABLE Persons (
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
#The DROP TABLE statement is used to drop an existing table in a database.
DROP TABLE Shippers;
The TRUNCATE TABLE statement is used to delete the data inside a table, but not the table itself.
#The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
ALTER TABLE table_name
ADD column_name datatype;
ALTER TABLE Customers
ADD Email varchar(255);
ALTER TABLE Customers
DROP COLUMN Email;
My SQL / Oracle (prior version 10G):
ALTER TABLE table_nameMODIFY COLUMN column_name datatype;
1. Insert the missing statement to get all the columns from the Customers table.
SELECT * FROM Customers;
2. Write a statement that will select the City column from the Customers table.
SELECT City FROM Customers
3. Select all the different values from the Country column in the Customers table.
SELECT DISTINCT Country FROM Customers
SELECT CustomerName,City FROM Customers;
SELECT DISTINCT Country FROM Customers; // contires
SELECT COUNT(DISTINCT Country) FROM Customers; //Counting country
#The WHERE clause is used to filter records.
SELECT * FROM Customers
WHERE Country='Mexico';
SELECT * FROM Products
WHERE Price BETWEEN 50 AND 60;
SELECT * FROM Customers
WHERE City LIKE 'm%';
#The WHERE clause can be combined with AND, OR, and NOT operators.
SELECT * FROM Customers
WHERE NOT Country='Germany';
# The AND and OR operators are used to filter records based on more than one condition:
- The AND operator displays a record if all the conditions separated by AND are TRUE.
- The OR operator displays a record if any of the conditions separated by OR is TRUE.
The NOT operator displays a record if the condition(s) is NOT TRUE.
#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 * FROM Customers
ORDER BY Country DESC;
SELECT * FROM Customers
ORDER BY Country, CustomerName;
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC
#The INSERT INTO statement is used to insert new records in a table.
SELECT * FROM Customers
WHERE NOT Country='Germany' AND NOT Country='USA';
SELECT * FROM Customers
WHERE Country='Germany' AND (City='Berlin' OR City='München');
# The INSERT INTO statement is used to insert new records in a table.
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
# A field with a NULL value is a field with no value.
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NULL;
#The UPDATE statement is used to modify the existing records in a table.
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
# The DELETE statement is used to delete existing records in a table.
DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';
#The SELECT TOP clause is used to specify the number of records to return.
SELECT * FROM Customers
LIMIT 3;
SELECT TOP 3 * FROM Customers;
SELECT * FROM Customers
WHERE ROWNUM <= 3;
SELECT TOP 50 PERCENT * FROM Customers;
# The MIN() function returns the smallest value of the selected column.
The MAX() function returns the largest value of the selected column.
SELECT MIN(Price) AS SmallestPrice
FROM Products;
#The COUNT() function returns the number of rows that matches a specified criteria.
The AVG() function returns the average value of a numeric column.
The SUM() function returns the total sum of a numeric column.
SELECT SUM(Quantity)
FROM OrderDetails;
SELECT AVG(Price)
FROM Products;
SELECT COUNT(ProductID)
FROM Products;
#The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
There are two wildcards often used in conjunction with the LIKE operator:
- % - The percent sign represents zero, one, or multiple characters
- _ - The underscore represents a single character
Note: MS Access uses an asterisk (*) instead of the percent sign (%), and a question mark (?) instead of the underscore (_).
The percent sign and the underscore can also be used in combinations!
LIKE Operator
Description
WHERE CustomerName LIKE 'a%'
Finds any values that start with "a"
WHERE CustomerName LIKE '%a'
Finds any values that end with "a"
WHERE CustomerName LIKE '%or%'
Finds any values that have "or" in any position
WHERE CustomerName LIKE '_r%'
Finds any values that have "r" in the second position
WHERE CustomerName LIKE 'a__%'
Finds any values that start with "a" and are at least 3 characters in length
WHERE ContactName LIKE 'a%o'
Finds any values that start with "a" and ends with "o"
#A wildcard character is used to substitute one or more characters in a string.
Wildcard characters are used with the SQL LIKE operator. The LIKE operator is used in a WHERE clause to search for a specified pattern in a column.
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
#The IN operator allows you to specify multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
#The BETWEEN operator selects values within a given range. The values can be numbers, text, or dates.
The BETWEEN operator is inclusive: begin and end values are included.
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20
AND NOT CategoryID IN (1,2,3);
SELECT * FROM Orders
WHERE OrderDate BETWEEN #01/07/1996# AND #31/07/1996#;
SELECT * FROM Orders
WHERE OrderDate BETWEEN '1996-07-01' AND '1996-07-31';
#SQL aliases are used to give a table, or a column in a table, a temporary name.
Aliases are often used to make column names more readable.
An alias only exists for the duration of the query.
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
#A JOIN clause is used to combine rows from two or more tables, based on a related column between them.
Let's look at a selection from the "Orders" table:
Here are the different types of the JOINs in SQL:
- (INNER) JOIN: Returns records that have matching values in both tables
- LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table
- RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table
- FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
#The INNER JOIN keyword selects records that have matching values in both tables.
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperNameFROM ((OrdersINNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
#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 Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
#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.
#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.
#A self JOIN is a regular join, but the table is joined with itself.
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2, A.City
FROM Customers A, Customers B
WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City
ORDER BY A.City;
#The UNION operator is used to combine the result-set of two or more SELECT statements.
- Each SELECT statement within UNION must have the same number of columns
- The columns must also have similar data types
- The columns in each SELECT statement must also be in the same order
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
#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 COUNT(CustomerID), Country
FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
# Constraints can be specified when the table is created with the CREATE TABLE statement, or after the table is created with the ALTER TABLE statement.
#SQL NOT NULL Constraint
By default, a column can hold NULL values.
The NOT NULL constraint enforces a column to NOT accept NULL value
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int
);
#The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a UNIQUE constraint.
SQL Server / Oracle / MS Access:
CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int);
MySQL:
CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, UNIQUE (ID));
ALTER TABLE Persons
ADD UNIQUE (ID);
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);
#The PRIMARY KEY constraint uniquely identifies each record in a table.
Primary keys must contain UNIQUE values, and cannot contain NULL
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
#A FOREIGN KEY is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.
#A FOREIGN KEY is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.
The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent 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.
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CHECK (Age>=18)
);
#The DEFAULT constraint is used to provide a default value for a column.
The default value will be added to all new records IF no other value is specified.
#Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.
Often this is the primary key field that we would like to be created automatically every time a new record is inserted.
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (Personid)
);
#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 Date Data Types
MySQL 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
- TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
- YEAR - format YYYY or YY
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
#SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page input
- SELECT - extracts data from a database
- UPDATE - updates data in a database
- DELETE - deletes data from a database
- INSERT INTO - inserts new data into a database
- CREATE DATABASE - creates a new database
- ALTER DATABASE - modifies a database
- CREATE TABLE - creates a new table
- ALTER TABLE - modifies a table
- DROP TABLE - deletes a table
- CREATE INDEX - creates an index (search key)
- DROP INDEX - deletes an index
#The following constraints are commonly used in SQL:
- NOT NULL - Ensures that a column cannot have a NULL value
- UNIQUE - Ensures that all values in a column are different
- PRIMARY KEY - A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
- FOREIGN KEY - Uniquely identifies a row/record in another table
- CHECK - Ensures that all values in a column satisfies a specific condition
- DEFAULT - Sets a default value for a column when no value is specified
- INDEX - Used to create and retrieve data from the database very quickly
Operators in The WHERE Clause
The following operators can be used in the WHERE clause:
| Operator | Description | Example |
|---|---|---|
| = | Equal | Try it |
| > | Greater than | Try it |
| < | Less than | Try it |
| >= | Greater than or equal | Try it |
| <= | Less than or equal | Try it |
| <> | Not equal. Note: In some versions of SQL this operator may be written as != | Try it |
| BETWEEN | Between a certain range | Try it |
| LIKE | Search for a pattern | Try it |
| IN | To specify multiple possible values for a column |
SQL Keywords
Keyword
Description
ADD
Adds a column in an existing table
ADD CONSTRAINT
Adds a constraint after a table is already created
ALTER
Adds, deletes, or modifies columns in a table, or changes the data type of a column in a table
ALTER COLUMN
Changes the data type of a column in a table
ALTER TABLE
Adds, deletes, or modifies columns in a table
ALL
Returns true if all of the subquery values meet the condition
AND
Only includes rows where both conditions is true
ANY
Returns true if any of the subquery values meet the condition
AS
Renames a column or table with an alias
ASC
Sorts the result set in ascending order
BACKUP DATABASE
Creates a back up of an existing database
BETWEEN
Selects values within a given range
CASE
Creates different outputs based on conditions
CHECK
A constraint that limits the value that can be placed in a column
COLUMN
Changes the data type of a column or deletes a column in a table
CONSTRAINT
Adds or deletes a constraint
CREATE
Creates a database, index, view, table, or procedure
CREATE DATABASE
Creates a new SQL database
CREATE INDEX
Creates an index on a table (allows duplicate values)
CREATE OR REPLACE VIEW
Updates a view
CREATE TABLE
Creates a new table in the database
CREATE PROCEDURE
Creates a stored procedure
CREATE UNIQUE INDEX
Creates a unique index on a table (no duplicate values)
CREATE VIEW
Creates a view based on the result set of a SELECT statement
DATABASE
Creates or deletes an SQL database
DEFAULT
A constraint that provides a default value for a column
DELETE
Deletes rows from a table
DESC
Sorts the result set in descending order
DISTINCT
Selects only distinct (different) values
DROP
Deletes a column, constraint, database, index, table, or view
DROP COLUMN
Deletes a column in a table
DROP CONSTRAINT
Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint
DROP DATABASE
Deletes an existing SQL database
DROP DEFAULT
Deletes a DEFAULT constraint
DROP INDEX
Deletes an index in a table
DROP TABLE
Deletes an existing table in the database
DROP VIEW
Deletes a view
EXEC
Executes a stored procedure
EXISTS
Tests for the existence of any record in a subquery
FOREIGN KEY
A constraint that is a key used to link two tables together
FROM
Specifies which table to select or delete data from
FULL OUTER JOIN
Returns all rows when there is a match in either left table or right table
GROUP BY
Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM, AVG)
HAVING
Used instead of WHERE with aggregate functions
IN
Allows you to specify multiple values in a WHERE clause
INDEX
Creates or deletes an index in a table
INNER JOIN
Returns rows that have matching values in both tables
INSERT INTO
Inserts new rows in a table
INSERT INTO SELECT
Copies data from one table into another table
IS NULL
Tests for empty values
IS NOT NULL
Tests for non-empty values
JOIN
Joins tables
LEFT JOIN
Returns all rows from the left table, and the matching rows from the right table
LIKE
Searches for a specified pattern in a column
LIMIT
Specifies the number of records to return in the result set
NOT
Only includes rows where a condition is not true
NOT NULL
A constraint that enforces a column to not accept NULL values
OR
Includes rows where either condition is true
ORDER BY
Sorts the result set in ascending or descending order
OUTER JOIN
Returns all rows when there is a match in either left table or right table
PRIMARY KEY
A constraint that uniquely identifies each record in a database table
PROCEDURE
A stored procedure
RIGHT JOIN
Returns all rows from the right table, and the matching rows from the left table
ROWNUM
Specifies the number of records to return in the result set
SELECT
Selects data from a database
SELECT DISTINCT
Selects only distinct (different) values
SELECT INTO
Copies data from one table into a new table
SELECT TOP
Specifies the number of records to return in the result set
SET
Specifies which columns and values that should be updated in a table
TABLE
Creates a table, or adds, deletes, or modifies columns in a table, or deletes a table or data inside a table
TOP
Specifies the number of records to return in the result set
TRUNCATE TABLE
Deletes the data inside a table, but not the table itself
UNION
Combines the result set of two or more SELECT statements (only distinct values)
UNION ALL
Combines the result set of two or more SELECT statements (allows duplicate values)
UNIQUE
A constraint that ensures that all values in a column are unique
UPDATE
Updates existing rows in a table
VALUES
Specifies the values of an INSERT INTO statement
VIEW
Creates, updates, or deletes a view
WHERE
Filters a result set to include only records that fulfill a specified condition
No comments:
Post a Comment