SQL Fundamentals

Introduction to SQL

  • SQL (Structured Query Language) is a standard language for creating and manipulating databases.
  • Initially developed between 1974-1977.
  • It is an ANSI and ISO standard, used across various RDBMS such as DB2, Oracle, MS Access, and MS SQL Server.
  • Allows users to create, update, delete, and retrieve data. SQL is simple, easy to learn, and consists of standard English words.

Advantages and Disadvantages of SQL

  • Advantages:
    • Portable: Runs on mainframes, PCs, laptops, servers, and mobile phones.
    • Easy to learn and understand: Uses English statements.
    • Compatible with any DBMS system.
    • No coding needed: Simplifies database management.
    • Multiple data views: Provides different views of database structure and content for different users.
  • Disadvantages:
    • Requires detailed knowledge of database structure.
    • Can provide misleading results.
    • Difficult interface for some users.

Types of SQL Commands

  • DDL (Data Definition Language): Used to create and modify the structure of database objects.
    • CREATE, ALTER, DROP
  • DML (Data Manipulation Language): Used to manage data within database objects.
    • UPDATE, INSERT, DELETE, SELECT
  • DCL (Data Control Language): Used to control access to the database.
    • GRANT, REVOKE
  • TCL (Transaction Control Language): Used to manage transactions in the database.
    • COMMIT, ROLLBACK

SQL Data Types

  • INT: Whole numbers (e.g., TouristID INT).
  • FLOAT: Numbers with decimals (e.g., TicketPrice FLOAT).
  • DECIMAL(p, s): Exact decimal numbers (e.g., Revenue DECIMAL(10, 2)).
  • CHAR(n): Fixed length text (e.g., State CHAR(3)).
  • VARCHAR(n): Variable length text (e.g., TouristName VARCHAR(100)).
  • TEXT: Long text (e.g., Feedback TEXT).
  • DATE: Dates (e.g., VisitDate DATE).
  • DATETIME: Date and time (e.g., BookingTime DATETIME).
  • TIMESTAMP: Date and time with time zone (e.g., LastUpdate TIMESTAMP).

DDL Commands

CREATE DATABASE

  • Used to create a new database.
  • Syntax: CREATE DATABASE database-name;
  • Example: CREATE DATABASE mydatabase;

USE DATABASE

  • Used to select a specific database.
  • Syntax: USE database_name;
  • Example: USE mydatabase;

CREATE TABLE

  • Used to create a new table in a database.
  • Syntax:
  CREATE TABLE table_name (
  column1 datatype,
  column2 datatype,
  ....
  );
  • Column parameters specify the column's name.
  • Datatype parameters specify the type of data the column can hold.

SQL Constraints

  • Rules enforced on data columns to ensure accuracy and reliability.
  • Can be at the column or table level.
  • Specified during table creation (CREATE TABLE) or modification (ALTER TABLE).

Constraint Types

  • PRIMARY KEY: Uniquely identifies each row; automatically applies NOT NULL and UNIQUE.
  • FOREIGN KEY: Links two tables together, referencing the PRIMARY KEY in another table.
  • NOT NULL: Ensures a column cannot have a NULL value.
  • UNIQUE: Ensures all values in a column are different.
  • CHECK: Ensures all values in a column satisfy specific conditions.
  • DEFAULT: Provides a default value for a column when none is specified.

Primary Key

  • Single Column:
    • Column Level:
      CREATE TABLE department ( dept_id int(3) PRIMARY KEY, dept_name varchar(20) );
    • Table Level:
      CREATE TABLE department ( dept_id int(3), dept_name varchar(20), PRIMARY KEY (dept_id) );
  • Composite Key (Table Level):
CREATE TABLE CUSTOMERS (
ID INT,
NAME VARCHAR (20),
AGE INT,
ADDRESS CHAR (25),
SALARY DECIMAL (18, 2),
PRIMARY KEY (ID, NAME)
);

Foreign Key

CREATE TABLE employee (
emp_id VARCHAR (5) PRIMARY KEY,
emp_name VARCHAR (15),
entrydate TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
salary DECIMAL (7,0),
dept_id int(3),
FOREIGN KEY (dept_id) REFERENCES department (dept_id)
);

ALTER TABLE

  • Modify table structure.
    • Add, modify, or delete columns.
    • Add or drop constraints.
ADD COLUMN
ALTER TABLE table_name
ADD column_name column-definition;
MODIFY COLUMN
ALTER TABLE table_name
MODIFY column_name column_type;
DROP COLUMN
ALTER TABLE table_name
DROP COLUMN column_name;
RENAME COLUMN
ALTER TABLE table_name
CHANGE old_name new_name data_type;

DML Commands

INSERT INTO

INSERT INTO table_name (column1, column2,...)
VALUES (value1, value2,...);

UPDATE

UPDATE table_name
SET column_name = new_value
WHERE column_name = some_value;

DELETE

DELETE FROM table_name
WHERE column_name = some_value;

TRUNCATE TABLE

  • Removes all rows from a table, leaving the structure intact.
TRUNCATE TABLE table_name;

SELECT

SELECT column_name(s)
FROM table_name;
  • SELECT DISTINCT : Retrieves unique rows.
  • WHERE clause: Filters rows based on specified conditions.
  • ORDER BY clause: Sorts the result set.
  • LIKE operator: Used for wildcard searches (% for zero or more characters and _ for a single character).
  • Logical Operators : AND, OR

Aggregate Functions

  • COUNT: Returns the number of rows.
  • SUM: Returns the sum of values.
  • AVG: Returns the average value.
  • MAX: Returns the maximum value.
  • MIN: Returns the minimum value.
  • GROUP BY clause: Groups rows based on a specified column.
  • HAVING clause: Filters groups based on a condition.

INTERSECT

  • Returns common records from two or more tables.