SQL Fundamentals

SQL Overview

• SQL is a computer language for storing, manipulating, and retrieving data stored in relational databases.
• It is the standard language for Relational Database Management Systems.
• All relational database management systems like MySQL, MS Access, Oracle, Sybase, Informix, and SQL Server use SQL as a standard database language.

SQL Syntax

• SQL statements start with keywords like SELECT, INSERT, UPDATE, DELETE, ALTER, DROP, CREATE, USE, and SHOW, and end with a semicolon (;).
• SQL is case insensitive, which means SELECT and select have the same meaning in SQL statements.

SQL Process

• The SQL process involves determining the best way to carry out a request, and the SQL engine figures out how to interpret the task.
• The process involves various components, including Query Dispatcher, Optimization Engines, Classic Query Engine, and SQL Query Engine.

Types of SQL Language

• Data Manipulation Language (DML) retrieves or modifies data.
• Data Definition Language (DDL) defines the structure of the data.
• Data Control Language (DCL) defines the privileges granted to database users.

Data Manipulation Language (DML)

• DML modifies the database instance by inserting, updating, and deleting data.
• It includes commands like:
  • SELECT/FROM/WHERE
  • INSERT INTO/VALUES
  • UPDATE/SET/WHERE
  • DELETE FROM/WHERE

Data Definition Language (DDL)

• DDL defines the database schema.
• It includes commands like:
  • CREATE
  • DROP
  • ALTER

SQL Constraints

• NOT NULL Constraint: Ensures a column cannot have a NULL value.
• DEFAULT Constraint: Provides a default value for a column.
• UNIQUE Constraint: Ensures all values in a column are different.
• PRIMARY KEY: Uniquely identifies each row in a database table.
• FOREIGN KEY: Uniquely identifies a row in another database table.
• CHECK Constraint: Ensures all values in a column satisfy certain conditions.
• INDEX: Used to create and retrieve data from the database quickly.

SQL Basic Language Elements

• Statements: Have a persistent effect on schemas and data, or control transactions, program flow, connections, sessions, or diagnostics.
• Queries: Retrieve data based on specific criteria.
• Expressions: Can produce scalar values or tables consisting of columns and rows of data.
• Clauses: Are optional, constituent components of statements and queries.
• Whitespace is generally ignored in SQL statements and queries.

SQL Statements/Commands

• Data Manipulation:
  • SELECT: Retrieves data from the database.
  • INSERT: Adds new rows of data to the database.
  • DELETE: Removes rows of data from the database.
  • UPDATE: Modifies existing database data.
• Data Definition:
  • CREATE TABLE: Adds a new table to the database.
  • DROP TABLE: Removes a table from the database.
  • ALTER TABLE: Changes the structure of an existing table.
  • CREATE VIEW: Adds a new view to the database.
  • DROP VIEW: Removes a view from the database.

SQL Data Types

• Character strings:
  • CHAR: Fixed-length character string.
  • VARCHAR: Variable-length character string.
  • TEXT: Variable-length character string.
• Unicode strings:
  • NCHAR: Fixed-length Unicode data.
  • NVARCHAR: Variable-length Unicode data.
  • NTEXT: Variable-length Unicode data.
• Binary types:
  • BIT: Allows 0, 1, or NULL.
  • BINARY: Fixed-length binary data.
  • VARBINARY: Variable-length binary data.
  • IMAGE: Variable-length binary data.

Extended Data Types

• Variable-length character strings
• Dates and times
• Boolean data

Constants

• Numeric constants: Written as ordinary decimal numbers.
• String constants: Enclosed in single quotes.
• Date and time constants: Written in a specific format.### SQL Basics
• SQL is a language used to manage relational databases.
• SQL is used to store, manipulate, and retrieve data stored in a database.

Creating Tables

• The CREATE TABLE statement is used to create a new table in a database.
• The syntax is: CREATE TABLE table_name (column1 datatype, column2 datatype, ...)
• Example: CREATE TABLE Customers (CustomerName varchar(255), ContactName varchar(255), ...).

Constraints

• Constraints are used to limit the type of data that can be inserted into a table.
• There are several types of constraints:
  • NOT NULL: Ensures that a column cannot have a null value.
  • DEFAULT: Provides a default value for a column when none is specified.
  • UNIQUE: Ensures that all values in a column are different.
  • PRIMARY KEY: Uniquely identifies each row in a table.
  • FOREIGN KEY: Links two tables together.
  • CHECK: Ensures that the data in a column satisfies a specific condition.
  • INDEX: Used to create and retrieve data quickly.

Modifying Tables

• The ALTER TABLE statement is used to modify an existing table.
• The syntax is: ALTER TABLE table_name ADD/MODIFY/DROP column_name datatype
• Example: ALTER TABLE Customers ADD Email varchar(255).

Dropping Tables

• The DROP TABLE statement is used to delete a table.
• The syntax is: DROP TABLE table_name
• Example: DROP TABLE Customers.

Deleting Data

• The DELETE statement is used to delete data from a table.
• The syntax is: DELETE FROM table_name WHERE condition
• Example: DELETE FROM Customers WHERE CustomerName = 'Alfreds Futterkiste'.

Updating Data

• The UPDATE statement is used to modify existing data in a table.
• The syntax is: UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition
• Example: UPDATE Customers SET ContactName = 'Alfred Schmidt', City = 'Frankfurt' WHERE CustomerID = 1.

Selecting Data

• The SELECT statement is used to select data from a table.
• The syntax is: SELECT column1, column2, ... FROM table_name WHERE condition
• Example: SELECT * FROM Customers WHERE Country = 'Germany'.

Filtering Data

• The WHERE clause is used to filter data based on a specific condition.
• The syntax is: SELECT column1, column2, ... FROM table_name WHERE condition
• Example: SELECT * FROM Customers WHERE Country = 'Germany' AND City = 'Berlin'.

Sorting Data

• The ORDER BY clause is used to sort data in ascending or descending order.
• The syntax is: SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC
• Example: SELECT * FROM Customers ORDER BY Country ASC.

Limiting Data

• The LIMIT or TOP clause is used to limit the number of records returned.
• The syntax is: SELECT column1, column2, ... FROM table_name LIMIT number or SELECT TOP number FROM table_name
• Example: SELECT * FROM Customers LIMIT 3 or SELECT TOP 3 FROM Customers.

Joining Tables

• The JOIN clause is used to combine data from two or more tables.
• The syntax is: SELECT column1, column2, ... FROM table1 JOIN table2 ON table1.column_name = table2.column_name
• Example: SELECT * FROM Customers JOIN Orders ON Customers.CustomerID = Orders.CustomerID.