What is SQL? 🤔
SQL stands for Structured Query Language and it lets you accessing and manipulating database systems. SQL became a standard of the American National Standards Institute (ANSI) in 1986, and of the International Organization for Standardization (ISO) in 1987.
Short for American National Standards Institute, ANSI was founded in 1918, and today is a voluntary organization composed of over 1,300 members that establish standards for the computer industry.
What SQL can do? 🧐
CREATE DATABASE: Creates a new database.
USE command: To select and open an already existing database.
CREATE TABLE: Creates a new table in a database.
ALTER TABLE: Modifies a table.
DROP TABLE: Deletes a table.
SELECT statement: To extract information from the table.
INSERT INTO statement: To insert new data into a table.
UPDATE statement: To modify data in a table.
DELETE: To delete data(tuples) from a table(not the column).
- Tuple: A row in a table represents the record of relation and known as a tuple of a relation.
- Attribute: A column header of a table is known as an attribute of a relation.
Data types in SQL 🕵️♂️
INTEGER: Used to store numeric values.
CHARACTER(CHAR(SIZE)).
VARCHAR(X): Used to store STRINGS.
DATE: Used to store a date in “yyyy/mm/dd" format.
BOOLEAN: Used to store values as TRUE or FALSE
TIME: Used to store time in "hh:mm:ss" format.
Database 🧮
How you can create a database? Well that's simple
CREATE DATABASE <name-of-database>;
Now to use that database.
USE <database-name>;
To remove any database.
DROP DATABASE <database-name>;
Tables 🪠
To Create a SQL table,
CREATE TABLE <name-of-table>
(<COLUMN-NAME> <DATA-TYPE><SIZE>, <COLUMN-NAME> <DATA-TYPE><SIZE>);
Insert value into table columns.
INSERT INTO <table-name>
VALUES(<valuel>,<value2>.so on);
Display data of the table.
SELECT * FROM <table>;
Modify
To modify data in a table or to make changes, we use the UPDATE statement.
UPDATE <table-name> SET <column>:<value> WHERE <column 2>:<value>;
Here, we are specifying the rows to be modified using the WHERE clause and the new data is written into the respective record using the SET keyword.
SQL Operators
While working with the SELECT statement using the WHERE clause, a condition-based query is processing is carried out.
Arithmetic Operators.
Relational Operators.
Logical Operators.
Special Operators (Condition based).
Arithmetic Operators
Utility: To perform arithmetic operations like +, -, *./.%.
How to:
SELECT 5 + 10; =15
SELECT 5*4 FROM DUAL;
// DUAL is default table in MYSQL.
SELECT Name, Marks+10 FROM student;
This will display Names and Marks for all Students increased by 10.
Relational Operators
Utility: To perform a comparison between two values.
= Equal to
Greater than < Less than <>, != Not equal to
SELECT * FROM student WHERE Marks>80;
This will display the details of students whose marks are greater than 80.
Logical Operators
Utility: To combine multiple conditions and display data accordingly.
List of logical operators AND, OR, NOT operator.
SELECT * FROM students WHERE Marks > 80 and Gender='M';
To list all the details of students who scored more than 80 and are male.
SELECT * FROM students WHERE NOT (Stream="Science");
To list all the details of students who are not in the science stream more.
SPECIAL OPERATORS 🤷♂️
Condition based on Range: BETWEEN AND
Condition based on List: IN
Condition based on Pattern: LIKE
BETWEEN AND: SELECT <column-name> FROM <table-name> WHERE <column> BETWEEN <value 1> AND <value2>
SYNTAX for IN: SELECT <column-name> FROM <table-name> WHERE <colum> IN (value,value2,...)
Pattern based : SELECT <column-name> FROM <table-name> WHERE <colum> LIKE <pattern>
SQL JOINS
An SQL JOIN clause is used to combine rows from two or more tables, based on a common field between them.
- Cartesian Product: Performed when no condition exists or is invalid, it joins all the rows in the first table with the second table.
A={1,2} & B={3,4} = { (1,3),(1,4),(2,3),(2,4) }
- Equi Joins: It uses equal sign as a comparison operator between two tables on basis of common field.
SELECT <column 1>,<column 2>,..
FROM <table>,<table2> WHERE <table.column]>=<table2.column2>;
- UNION Utility: The UNION operator is used to combine the result-set of two or more SELECT statements.
The number of columns and data types selected from each table should be the same.
SELECT Name FROM boys WHERE Rollno < 12 UNION SELECT Name FROM girls WHERE Rollno>6;
This will join the column of table 1 which has Rollno smaller than 12 with the column of table 2 having Rollno greater than 6.
SQL CHEATSHEET 👀
Aggregate Functions
COUNT: Return the number of rows in a certain table/view.
SUM: Accumulate the values.
AVG: Returns the average for a group of values.
MIN: Returns the smallest value of the group.
MAX: Returns the largest value of the group.
Basic Keywords 🤓
SELECT: Used to state which columns to query. Use * for all
FROM: Declares which table/view etc to select from WHERE Introduces a condition. Used for comparing a value to a specified input.
LIKE: Special operator used with the WHERE clause to search for a specific pattern in a column.
GROUP BY: Arranges identical data into groups.
HAVING: Specifies that only rows where aggregate values meet the specified conditions should be returned. Used because the WHERE keyword cannot be used with aggregate functions.
INNER JOIN: Returns all rows where a key record of one table is equal to key records of another.
LEFT JOIN: Returns all rows from the ‘left’ (1st) table with the matching rows in the right (2nd).
RIGHT JOIN: Returns all rows from the ‘right’ (2nd) table with the matching rows in the left (1st).
FULL OUTER JOIN: Returns rows that match either in the left or right table.
Querying data from a tables 👾
A database table is a set of data elements (values) stored in a model of vertical columns and horizontal rows. Use any of the below to query a table in SQL:
SELECT c1 FROM t: Select data in column c1 from a table named t
SELECT * FROM t: Select all rows and columns from a table named t
SELECT c1 FROM t WHERE c1 = ‘test’: Select data in column c1 from a table named t where the value in c1 = ‘test’
SELECT c1 FROM t ORDER BY c1 ASC (DESC): Select data in column c1 from a table name t and order by c1, either in ascending (default) or descending order
SELECT c1 FROM t ORDER BY c1LIMIT n OFFSET offset: Select data in column c1 from a table named t and skip offset of rows and return the next n rows
SELECT c1, aggregate(c2) FROM t GROUP BY c1: Select data in column c1 from a table named t and group rows using an aggregate function
SELECT c1, aggregate(c2) FROM t GROUP BY c1HAVING condition: Select data in column c1 from a table named t and group rows using an aggregate function and filter these groups using the ‘HAVING’ clause.
Thank you! 😇