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:


1 comment:

Taylor said...

I just have to introduce this hacker that I have been working with him on getting my credit score been boosted across the Equifax, TransUnion and Experian report. He made a lot of good changes on my credit report by erasing all the past eviction, bad collections and DUI off my credit report history and also increased my FICO score above 876 across my three credit bureaus report you can contact him for all kind of hacks . Email him here support@wavedrive.tech go on their website wavedrive.tech for more details,Whatsapp No:+14106350697 if you want to chat them up,One thing i can assure you would not regret this at all he is 100% legit