SQL Fundamentals

• Data and Database Systems are essential for managing and manipulating data effectively.

Fundamentals of SQL

• SQL (Structured Query Language) is the standard language for managing and manipulating relational databases.
• It helps users interact with databases by creating, reading, updating, and deleting data.

Key SQL Concepts

• Queries are specific requests for data retrieval or modification issued to the Database Management System (DBMS).
• SQL (Structured Query Language) is a programming language designed for managing relational databases and can be pronounced as "S-Q-L" or "sequel."
• SQL is commonly used for creating databases and tables, managing and manipulating data, and querying data for reports and analysis.

Common DBMS Platforms

• Microsoft SQL Server is a robust database system widely used for enterprise applications.
• MySQL is an open-source database system used in web applications.
• Oracle RDBMS is a powerful system known for handling large-scale applications.
• Microsoft Access is a simpler DBMS often used for small business applications.

SQL Data Types

• SQL includes data types for:
  • Exact Numeric: bigint, bit, decimal, int, money, numeric
  • Approximate Numeric: float, real
  • Date and Time: date, datetime, time
  • Character Strings: char, text, varchar
  • Unicode Strings: nchar, ntext, nvarchar
  • Binary Strings: binary, image, varbinary
  • Other Data Types: cursor, sql_variant, table, xml

SQL Operators

• SQL includes operators for:
  • Arithmetic: +, -, *, /, %
  • Comparison: =, >, <, >=, <=, <>
  • Compound: +=, -=, *=, /=, %=
  • Logical: AND, OR, NOT, LIKE, IN, BETWEEN, EXISTS, ANY, ALL

SQL Data Definition Commands

• SQL provides several commands to define and manipulate database structures.

Creating and Deleting Databases

• CREATE DATABASE creates a new database:

CREATE DATABASE myDB;

• DROP DATABASE deletes an existing database:

DROP DATABASE myDB;

Creating and Deleting Tables

• CREATE TABLE command defines a new table structure:

CREATE TABLE Students (
 StudentID VARCHAR(11),
 LastName VARCHAR(99),
 FirstName VARCHAR(99),
 Section VARCHAR(5)
);

• DROP TABLE deletes a table and its structure:

DROP TABLE Students;

• TRUNCATE TABLE deletes all data from a table but keeps its structure:

TRUNCATE TABLE Students;

Modifying Tables

• ALTER TABLE adds, deletes, or modifies columns. Includes operations such as:
  • Adding a column:

ALTER TABLE Students ADD MiddleName VARCHAR(99);

- Deleting a column:

ALTER TABLE Students DROP COLUMN Section;

- Modifying a column data type:

ALTER TABLE Students ALTER COLUMN MiddleName NVARCHAR(99);

SQL Constraints

• Constraints enforce rules on data stored in a table to maintain data integrity.
• NOT NULL Constraint

CREATE TABLE Students (
 StudentID VARCHAR(11) NOT NULL,
 LastName VARCHAR(99) NOT NULL,
 FirstName VARCHAR(99) NOT NULL,
 Section VARCHAR(5)
);

• PRIMARY KEY Constraint

ALTER TABLE Students ADD PRIMARY KEY (StudentID);

• FOREIGN KEY Constraint

ALTER TABLE Orders ADD FOREIGN KEY (CustomerID) REFERENCES Customers (CustomerID);

• CHECK Constraint

ALTER TABLE Students ADD CHECK (Age >= 15);

• DEFAULT Constraint

ALTER TABLE Students ADD CONSTRAINT df_section DEFAULT 'Not yet enrolled' FOR Section;

SQL Data Manipulation Commands

• SQL data manipulation commands allow users to insert, retrieve, update, and delete records in a database.
• It is recommended to understand and apply SQL data manipulation commands (INSERT, SELECT, UPDATE, DELETE).
• Utilize SQL operators such as DISTINCT, WHERE, IS NULL, LIKE, IN, and BETWEEN.
• Use ORDER BY for sorting retrieved data.

Inserting Records With INSERT INTO

• The INSERT INTO command inserts new rows into a table.
• Syntax:

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

• Example:

INSERT INTO Students (LastName, Section) VALUES ('Reyes', 'IT102');

• If inserting values into all columns:

INSERT INTO Students VALUES ('Reyes', 'IT102');

Retrieving Data With SELECT

• The SELECT command is used to fetch data from a table.
• Syntax:

SELECT column1, column2 FROM table_name;

• Example:

SELECT LastName, Section FROM Students;

• To select all columns:

SELECT * FROM Students;

• DISTINCT retrieves unique values from a column:

SELECT DISTINCT Section FROM Students;

• WHERE filters records based on conditions:

SELECT * FROM Students WHERE Section = 'IT101';

Checking for Empty Fields With IS NULL

• Finds records where a specific field has no value.
• Syntax:

SELECT LastName, Section FROM Students WHERE Section IS NULL;

Searching for Patterns With LIKE

• Finds records matching a pattern using wildcards (% for multiple characters, _ for a single character).
• Example:

SELECT * FROM Students WHERE LastName LIKE '_b%';

• This retrieves students whose last names have 'b' in the second position.

Filtering Based on a List With IN

• Checks if a value is within a set of values.
• Example:

SELECT * FROM Students WHERE Section IN ('IT101', 'IT102', 'IT103');

Selecting a Range With BETWEEN

• Selects values within a specific range.
• Example:

SELECT * FROM Students WHERE Age BETWEEN 13 AND 15;

Sorting Results With ORDER BY

• Sorts records in ascending (default) or descending order.
• Example:

SELECT * FROM Students ORDER BY LastName;

• You can also sort by descending order:

SELECT * FROM Students ORDER BY LastName DESC;

Modifying Existing Records With UPDATE

• Modifies one or more records in a table.
• Example:

UPDATE Students SET Section = 'IT202', Status = 'Irregular' WHERE StudentID = '2018-100013';

Removing Records With DELETE

• Removes records from a table.
• Example:

DELETE FROM Students WHERE StudentID = '2018-100013';

• To delete all records:

DELETE FROM Students;