Monday 24 January 2022

SQL Cheat Sheet

Statements

 

AS

Used to assign an alias name to a column or a table. Alias is temporary, it exists only during the execution of the query.
 
select CustomerName as customer from customers;
 
AS is optional but is recommended to use it explicitly. So this query is also valid:

select CustomerName customer from customers;

DROP

Deletes an object (database, table, index or view)
 
 
To drop a database:
 
> drop database northwind;

 
MySQL example how to verify that e.g. database was dropped: 

> show databases;

or 

> select schema_name from information_schema.schemata;


SELECT 


To return all unique last names:
 
SELECT DISTINCT LastName FROM Employees;



Clauses

ORDER BY

 
To sort the output by the LastName field, then by the FirstName field if LastName matches:
 
SELECT * FROM Employees ORDER BY LastName, FirstName;

 

JOIN 


 

...
 

https://www.db-fiddle.com/f/tPUgzPgpCwo1qKF9HUFn2v/0


CREATE TABLE Persons_dev (

    PersonID int,

      Name varchar(255)

);


CREATE TABLE Persons_beta (

    PersonID int,

      Name varchar(255)

);


INSERT INTO Persons_dev (PersonID, Name) VALUES (1,'Ana');

INSERT INTO Persons_dev (PersonID, Name) VALUES (2,'Bojan');

INSERT INTO Persons_dev (PersonID, Name) VALUES (3,'Ceca');

INSERT INTO Persons_dev (PersonID, Name) VALUES (4,'Dejan');

INSERT INTO Persons_dev (PersonID, Name) VALUES (5,'Erik');

INSERT INTO Persons_dev (PersonID, Name) VALUES (6,'Florian');


INSERT INTO Persons_beta (PersonID, Name) VALUES (0,'Zero');

INSERT INTO Persons_beta (PersonID, Name) VALUES (1,'Ana');

INSERT INTO Persons_beta (PersonID, Name) VALUES (2,'Bojan');

INSERT INTO Persons_beta (PersonID, Name) VALUES (3,'Ceca');

INSERT INTO Persons_beta (PersonID, Name) VALUES (4,'Deki');

INSERT INTO Persons_beta (PersonID, Name) VALUES (5,'Erik');



SELECT * FROM Persons_dev

WHERE PersonID NOT IN (SELECT PersonID FROM Persons_beta)

UNION 

SELECT * FROM Persons_beta

WHERE PersonID NOT IN (SELECT PersonID FROM Persons_dev)


SELECT Persons_dev.PersonID, Persons_dev.Name, Persons_beta.Name FROM Persons_dev 

INNER JOIN Persons_beta ON Persons_dev.PersonID=Persons_beta.PersonID

WHERE Persons_dev.Name != Persons_beta.Name;



https://www.db-fiddle.com/f/tPUgzPgpCwo1qKF9HUFn2v/3


 



Operators

[NOT] IN


To return all published posts that are present in posts_dev table but not in posts_beta (id is the key):

SELECT * 
FROM posts_dev
WHERE post_status='publish' AND 
              id NOT IN (SELECT ID FROM posts_beta WHERE post_status='publish')
 

Comments

Comments (e.g. in .sql files) are:
 
-- This is a comment in one line

or 

/* This is a comment in one line */

or 

/* 
This is 
a comment 
in multiple lines 
*/

References:


No comments: