Points of SQL

Schema Operations

Constraint

  • NOT NULL
  • UNIQUE
  • PRIMARY KEY
  • FOREIGN KEY
  • CHECK - Ensures that the value in a column meets a specific condition
  • DEFAULT - Specifies a default value for a column

Create

1
CREATE DATABASE dbname
1
2
3
4
5
6
7
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);

Alter

1
2
3
4
ALTER TABLE table_name
ADD column_name datatype
ALTER COLUMN column_name datatype
DROP COLUMN column_name

Create

Read

WHERE

GROUP BY

Provide filters for aggregate function, make aggregate function work for some keys instead of the whole table.

1
2
3
SELECT column_name, aggregate_function(column_name)
FROM table_name
GROUP BY column_name;

HAVING

ORDER BY

Aggregate Functions

Update

Delete

Joining Tables

INNER JOIN

Join only on common values

1
2
3
SELECT *
FROM table1 INNER JOIN table2
    ON table1.pk = table2.fk