Shubham's Blog

1. DBMS-Basic SQL CheatSheet

Shubham Khuntia's photo
Shubham Khuntia
·

4 min read

1. DBMS-Basic SQL CheatSheet

Photo by Pete Pedroza on Unsplash

Table of contents

Basic SQL

SHOW DATABASES;

USE mysql;

SHOW CREATE DATABASE mysql;

SHOW TABLES;

DESCRIBE user;

SHOW CREATE TABLE servers;

SHOW COLUMNS FROM servers;
CREATE DATABASE
CREATE DATABASE MovieIndustry;

CREATE DATABASE IF NOT EXISTS MovieIndustry;

SHOW DATABASES;

DROP DATABASE MovieIndustry;
Data Types

  • Numeric e.g., INT, BIGINT, TINYINT, DECIMAL, etc.

  • Date and Time e.g., DATE, TIME, TIMESTAMP, YEAR, etc.

  • String e.g., VARCHAR, CHAR, ENUM, SET, BLOB, etc.

  • JSON e.g., JSON

  • Spatial Data represents the location, size, and shape of an object on planet Earth such as a building, lake, mountain, or township. MySQL also supports spatial data types, e.g., GEOMETRY, POINT, etc.

CREATE TABLE
CREATE TABLE Actors (
FirstName VARCHAR(20),
SecondName VARCHAR(20),
DoB DATE,
Gender ENUM('Male','Female','Other'),
MaritalStatus ENUM('Married', 'Divorced', 'Single'),
NetWorthInMillions DECIMAL
);

SHOW TABLES;

DROP TABLE students



DESC Actors;

Doubt: columnName columnType [NOT NULL | NULL] [DEFAULT columnValue]

Inserting Data

INSERT INTO table (col1, col2 … coln) VALUES (val1, val2, … valn);

INSERT INTO Actors ( FirstName, SecondName, DoB, Gender, MaritalStatus, NetworthInMillions) VALUES ("Brad", "Pitt", "1963-12-18", "Male", "Single", 240.00);

INSERT INTO Actors ( FirstName, SecondName, DoB, Gender, MaritalStatus, NetworthInMillions) VALUES ("Jennifer", "Aniston", "1969-11-02", "Female", "Single", 240.00),("Angelina", "Jolie", "1975-06-04", "Female", "Single", 100.00),("Johnny", "Depp", "1963-06-09", "Male", "Single", 200.00);

INSERT INTO Actors VALUES (DEFAULT, "Dream", "Actress", "9999-01-01", "Female", "Single", 000.00);

Note that we used the DEFAULT keyword for the ID column. We could have also used NULL or 0 for MySQL to automatically assign the next higher integer in the sequence to the ID column of the new row.

  • We can insert a row with all default values. If a column doesn’t have a default value defined, it is assigned NULL as default. Consider the query below:
INSERT INTO Actors () VALUES ();

INSERT INTO Actors SET DoB="1950-12-12", FirstName="Rajnikanth", SecondName="",  Gender="Male", NetWorthInMillions=50,  MaritalStatus="Married";

Querying Data

LIKE Operator

SELECT * from Actors WHERE FirstName LIKE "Jen%";

The % symbol is a wildcard character that matches all strings.

SELECT * from Actors WHERE FirstName LIKE "_enn%";

We can use the underscore character to match exactly one character

Combining Conditions

SELECT * FROM Actors WHERE FirstName > "B" AND NetWorthInMillions > 200;

SELECT * FROM Actors WHERE (NOT NetWorthInMillions) = 200;

if we put parentheses as follows around the NOT operator, the result is an empty set. Applying NOT on a non-zero column value makes it a zero, and since zero isn’t equal to 200, no rows are displayed.

SELECT * FROM Actors WHERE FirstName > "B" XOR NetWorthInMillions > 200;

MySQL supports exclusive OR through the XOR operator. Exclusive OR returns true when one of the two conditions is true. If both conditions are true, or both are false, the result of the XOR operations is false.

ORDER BY

SELECT * FROM Actors ORDER BY FirstName;

SELECT * FROM Actors ORDER BY FirstName DESC;

SELECT * FROM Actors ORDER BY NetWorthInMillions, FirstName;

if a tie occurs based on the first sort key, it is broken using the second sort key.

SELECT * FROM Actors ORDER BY NetWorthInMillions DESC, FirstName ASC;

MySQL ignores case when comparing strings in the ORDER BY clause, which implies strings “Kim”, “kIm” and “kim” are treated equally. If we want ASCII comparison we need to specify the BINARY keyword before the sort key.

SELECT * FROM Actors ORDER BY BINARY FirstName;

The CAST function allows us to treat a column as a different type.

SELECT * FROM Actors ORDER BY CAST(NetWorthInMillions AS CHAR);

LIMIT Clause

SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 3;

To retrieve the next 4 richest actors after the top three.

SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 4 OFFSET 3;

SELECT FirstName, SecondName from Actors ORDER BY NetWorthInMillions DESC LIMIT 3,4;

LIMIT <offset>, <number_of_row_to_print>;

Deleting Data

DELETE FROM Actors WHERE FirstName="priyanka";

To delete the top three actresses by net worth

DELETE FROM Actors ORDER BY NetWorthInMillions DESC LIMIT 3;

Truncate

TRUNCATE Actors;

Updating Data

UPDATE Actors SET NetWorthInMillions=1;

UPDATE Actors SET NetWorthInMillions=5 ORDER BY FirstName LIMIT 3;

Primary Key and Indexes

In the case of a clustered index, the table rows are sorted and kept in a B-tree structure

Alterations

ALTER TABLE table

CHANGE oldColumnName newColumnName datatype restrictions;

ALTER TABLE Actors CHANGE FirstName First_Name varchar(120);

we can specify the default value for the column First_Name to be the string “Anonymous” as follows:

ALTER TABLE Actors MODIFY First_Name varchar(20) DEAULT 'Anonymous';
 // Both of them are same 
ALTER TABLE Actors CHANGE First_Name First_Name varchar(20) DEFAULT "Anonymous";

ALTER TABLE Actors ADD MiddleName varchar(100);

ALTER TABLE Actors DROP MiddleName;

We can also control the position of the new column within the table using the FIRST or AFTER keyword.

ALTER TABLE Actors ADD MiddleName varchar(100) FIRST;

ALTER TABLE Actors DROP MiddleName; 
ALTER TABLE Actors ADD MiddleName varchar(100) AFTER DoB;

ALTER TABLE Actors DROP MiddleName, ADD Middle_Name varchar(100);

ALTER INDEX

ALTER TABLE table ADD INDEX indexName (col1, col2, … coln);

ALTER TABLE Actors ADD INDEX nameIndex (FirstName);

// To drop a primary key and add another coln as pri key.

 ALTER TABLE Movies DROP PRIMARY KEY;
 ALTER TABLE Movies ADD PRIMARY KEY (Released);

ALTER TABLE oldTableName RENAME newTableName;

DROP TABLE IF EXISTS ActorsTable;

DROP TABLE IF EXISTS Table1, Table2, Table3;
``````mysql
DROP DATABASE IF EXISTS MovieIndustry;

Alias

SELECT FirstName AS PopularName from Actors;

SELECT CONCAT(FirstName,' ', SecondName) AS FullName FROM Actors;

SELECT CONCAT(FirstName,' ', SecondName) AS FullName FROM Actors ORDER BY FullName;

Notably, aliases for columns can’t be used in the WHERE clause but aliases for table can, as shown next.

SELECT FirstName FROM Actors AS tbl WHERE tbl.FirstName='Brad' AND tbl.NetWorthInMillions > 200;

we use tbl as the alias for the Actors table

We can also use the table alias in the SELECT clause before we actually define the alias.

SELECT tbl.FirstName FROM Actors AS tbl WHERE tbl.FirstName='Brad' AND tbl.NetWorthInMillions > 200;

SELECT t1.FirstName, t1.NetworthInMillionsFROM Actors AS t1,Actors AS t2WHERE t1.NetworthInMillions = t2.NetworthInMillionsAND t1.Id != t2.Id;

DISTINCT CLAUSE

DISTINCT is a post processing filter, meaning it is applied to the resulting rows of a query.

SELECT DISTINCT MaritalStatus from Actors;

SELECT DISTINCT MaritalStatus, FirstName from Actors;

Aggregate Methods

 SELECT COUNT(*) FROM Actors;

SELECT SUM(NetworthInMillions) FROM Actors;

SELECT AVG(NetWorthInMillions) FROM Actors;

SELECT MIN(NetWorthInMillions) FROM Actors;

SELECT MAX(NetWorthInMillions) FROM Actors;

SELECT MAX(FirstName) FROM Actors;SELECT MIN(FirstName) FROM Actors;

We can find the income disparity among actors using the standard deviation function STD or STDDEVas follows:

SELECT STDDEV(NetWorthInMillions) FROM Actors;

GROUP BY

Syntactically, the GROUP BY clause must appear after the FROM and WHERE clauses and is also evaluated after them. However, GROUP BY is evaluated before the ORDER BY, LIMIT, and HAVING clauses.

SELECT FirstName FROM Actors GROUP BY FirstName;

SELECT Gender, COUNT(*) FROM Actors GROUP BY Gender;

SELECT Gender FROM Actors GROUP BY Gender;

SELECT MaritalStatus, AVG(NetworthInMillions) FROM Actors GROUP BY MaritalStatus ORDER BY MaritalStatus ASC;

Having Clause

SELECT MaritalStatus, AVG(NetworthInMillions) AS NetWorth FROM Actors GROUP BY MaritalStatus HAVING NetWorth > 450 OR NetWorth < 250;
SQLMySQL