SQL

Normalization

1NF (First Normal Form) Rules • Each table cell should contain a single value. • Each record needs to be unique. 2NF (Second Normal Form) Rules • Rule 1- Be in 1NF • Rule 2- Single Column Primary Key 3NF (Third Normal Form) Rules • Rule 1- Be in 2NF • Rule 2- Has no transitive functional dependencies To move our 2NF table into 3NF, we again need to again divide our table. 4NF (Fourth Normal Form) Rules • If no database table instance contains two or more, independent and multivalued data describing the relevant entity, then it is in 4th Normal Form. 5NF (Fifth Normal Form) Rules • A table is in 5th Normal Form only if it is in 4NF and it cannot be decomposed into any number of smaller tables without loss of data. https://www.guru99.com/database-normalization.html

Stored Procedures

A _____ is a prepared SQL code that you can save, so the code can be reused over and over again. --Stored Procedure Syntax-- CREATE PROCEDURE procedure_name AS sql_statement GO; --Execute a Stored Procedure-- EXEC procedure_name;

Joins

A ______ clause is used to combine rows from two or more tables, based on a related column between them. • (INNER) JOIN: Returns records that have matching values in both tables. • LEFT (OUTER) JOIN: Return all records from the left table, and the matched records from the right table • RIGHT (OUTER) JOIN: Return all records from the right table, and the matched records from the left table • FULL (OUTER) JOIN: Return all records when there is a match in either left or right table

Foreign Key

A ______ is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table. The table containing the _____ is called the child table, and the table containing the candidate key is called the referenced or parent table. CREATE TABLE Orders ( OrderID int NOT NULL, OrderNumber int NOT NULL, PersonID int, PRIMARY KEY (OrderID), FOREIGN KEY (PersonID) REFERENCES Persons(PersonID) );

Primary Key

A table can have only one ______, which may consist of single or multiple fields. The following SQL creates a _____ on the "ID" column when the "Persons" table is created: CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int, PRIMARY KEY (ID) );

Constraints

Are used to specify rules for data in a table. CREATE TABLE table_name ( column1 datatype constraint, column2 datatype constraint, column3 datatype constraint, .... );

Not Null

By default, a column can hold NULL values. The _____ constraint enforces a column to NOT accept NULL values. CREATE TABLE Persons ( ID int NOT NULL, LastName varchar(255) NOT NULL, FirstName varchar(255) NOT NULL, Age int );

Distinct

Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (______) values. Example: SELECT DISTINCT column1, column2, ... FROM table_name;

Syntax

Most of the actions you need to perform on a database are done with SQL statements. SQL keywords are NOT case sensitive: select is the same as SELECT Some database systems require a semicolon at the end of each SQL statement. SELECT - extracts data from a database UPDATE - updates data in a database DELETE - deletes data from a database INSERT INTO - inserts new data into a database CREATE DATABASE - creates a new database ALTER DATABASE - modifies a database CREATE TABLE - creates a new table ALTER TABLE - modifies a table DROP TABLE - deletes a table CREATE INDEX - creates an index (search key) DROP INDEX - deletes an index

SQL

Structured Query Language. ______ is used to communicate with a database. According to ANSI (American National Standards Institute), it is the standard language for relational database management systems.

and & Or

The WHERE clause can be combined with _____, _____, and NOT operators. SELECT * FROM Customers WHERE Country='Germany' AND City='Berlin'; SELECT * FROM Customers WHERE City='Berlin' OR City='München'; SELECT * FROM Customers WHERE NOT Country='Germany';

update

The ____ statement is used to modify the existing records in a table. UPDATE Customers SET ContactName = 'Alfred Schmidt', City= 'Frankfurt' WHERE CustomerID = 1;

where

The _____ clause is used to extract only those records that fulfill a specified condition. SELECT column1, column2, ... FROM table_name WHERE condition; First Example: SELECT * FROM Customers WHERE CustomerID=1; Second Example: SELECT * FROM Customers WHERE CustomerName Like 'Lu%';

order by

The _____ keyword is used to sort the result-set in ascending or descending order. SELECT * FROM Customers ORDER BY Country;

Drop DB

The _____ statement is used to drop an existing SQL database. DROP DATABASE testDB;

Drop Table

The _____ statement is used to drop an existing table in a database. DROP TABLE Shippers;

Unique

The ______ constraint ensures that all values in a column are different. CREATE TABLE Persons ( ID int NOT NULL UNIQUE, LastName varchar(255) NOT NULL, FirstName varchar(255), Age int );

Create DB

The ______ statement is used to create a new SQL database. CREATE DATABASE testDB;

Create Table

The ______ statement is used to create a new table in a database. CREATE TABLE Persons ( PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255) );

delete

The _______ statement is used to delete existing records in a table. DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

insert

The _______ statement is used to insert new records in a table. INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');

Select

The _______ statement is used to query the database and retrieve selected data that match the criteria that you specify. Here is the format of a simple select statement: select "column1" [,"column2",etc] from "tablename" [where "condition"]; [] = optional Real Example: select first_name, last_name, grade from students;