SQL Tutorial Part 2: Keys, Constraints, and Advanced Queries

Welcome to the second part of our comprehensive SQL tutorial. In this section, we'll dive deeper into keys, constraints, and advanced SQL queries. We'll also include practice exercises to help solidify our understanding.

Keys in SQL

Primary Keys

A primary key is a column (or a set of columns) in a table that uniquely identifies each row in that table. Each table can have only one primary key, and it must not contain NULL values.

Example:

CREATE TABLE student (
  rollno INT PRIMARY KEY,
  name VARCHAR(50),
  marks INT NOT NULL,
  grade VARCHAR(1),
  city VARCHAR(20)
);

Foreign Keys

A foreign key is a column (or a set of columns) that refers to the primary key of another table. It helps maintain referential integrity between two related tables.

Example:

CREATE TABLE city (
  city_id INT PRIMARY KEY,
  city_name VARCHAR(50)
);

CREATE TABLE student (
  student_id INT PRIMARY KEY,
  student_name VARCHAR(50),
  city_id INT,
  FOREIGN KEY (city_id) REFERENCES city(city_id)
);

Constraints

Constraints are rules enforced on data columns to ensure the integrity and reliability of the data.

NOT NULL

Ensures that a column cannot have a NULL value.

Example:

CREATE TABLE temp1 (
  id INT NOT NULL
);

UNIQUE

Ensures that all values in a column are unique.

Example:

CREATE TABLE temp1 (
  id INT UNIQUE
);

INSERT INTO temp1 VALUES (101); -- Success
INSERT INTO temp1 VALUES (101); -- Error: Duplicate entry '101'

PRIMARY KEY

Combines a UNIQUE constraint and a NOT NULL constraint, ensuring that a column (or a combination of columns) uniquely identifies each row.

Example:

CREATE TABLE temp1 (
  id INT PRIMARY KEY
);

FOREIGN KEY

Prevents actions that would destroy links between tables.

Example:

CREATE TABLE temp (
  cust_id INT,
  FOREIGN KEY (cust_id) REFERENCES customer(id)
);

DEFAULT

Sets a default value for a column if no value is specified.

Example:

CREATE TABLE emp (
  id INT,
  salary INT DEFAULT 25000
);

INSERT INTO emp (id) VALUES (101);
SELECT * FROM emp;

Expected Output:

CHECK

Ensures that all values in a column satisfy a specific condition.

Example:

CREATE TABLE newTab (
  age INT CHECK (age >= 18)
);

Creating a Database for College & Inserting Data

Example:

CREATE DATABASE college;
USE college;

CREATE TABLE student (
  rollno INT PRIMARY KEY,
  name VARCHAR(50),
  marks INT NOT NULL,
  grade VARCHAR(1),
  city VARCHAR(20)
);

INSERT INTO student (rollno, name, marks, grade, city)
VALUES
(101, "SHAMBHU", 78, "C", "Pune"),
(102, "RAM", 81, "B", "Delhi"),
(103, "SONU", 91, "A", "Mumbai"),
(104, "SHAM", 60, "D", "Nagpur"),
(105, "ROHIT", 99, "A", "Chennai"),
(106, "BANTI", 85, "C", "Hyderabad");

Table Content for student

SELECT * FROM student;

Expected Output:

Selecting Data

Basic Syntax:

SELECT col1, col2 FROM table_name;

Select All Columns:

SELECT * FROM student;

Example:

SELECT name, marks FROM student;

Expected Output:

Select Distinct Values:

SELECT DISTINCT city FROM student;

Expected Output:

WHERE Clause

The WHERE clause is used to filter records based on a specified condition.

Examples:

SELECT * FROM student WHERE marks > 80;

Expected Output:

SELECT * FROM student WHERE city = "Mumbai";

Expected Output:

SELECT * FROM student WHERE marks > 80 AND city = "Mumbai";

Expected Output:

Using Operators in WHERE

• Arithmetic Operators: +, -, *, /, %

• Comparison Operators: =, !=, >, >=, <, <=

• Logical Operators: AND, OR, NOT, BETWEEN, LIKE, IN

Examples:

Arithmetic and Comparison Operators:

SELECT * FROM student WHERE marks + 10 > 100;

Expected Output:

AND Operator:

SELECT * FROM student WHERE marks > 90 AND city = "Mumbai";

Expected Output:

OR Operator:

SELECT * FROM student WHERE marks > 90 OR city = "Mumbai";

Expected Output:

BETWEEN Operator:

SELECT * FROM student WHERE marks BETWEEN 80 AND 90;

Expected Output:

IN Operator:

SELECT * FROM student WHERE city IN ("Delhi", "Mumbai");

Expected Output:

NOT Operator:

SELECT * FROM student WHERE city NOT IN ("Delhi", "Mumbai");

Expected Output:

LIMIT Clause:

SELECT * FROM student LIMIT 3;

Expected Output:

ORDER BY Clause:

SELECT * FROM student ORDER BY city ASC LIMIT 3;

Expected Output:

Aggregate Functions

Aggregate functions perform a calculation on a set of values and return a single value.

Examples:

COUNT():

SELECT COUNT(name) FROM student;

Expected Output:

MAX():

SELECT MAX(marks) FROM student;

Expected Output:

MIN():

SELECT MIN(marks) FROM student;

Expected Output:

SUM():

SELECT SUM(marks) FROM student;

Expected Output:

AVG():

SELECT AVG(marks) FROM student;

Expected Output:

GROUP BY Clause

The GROUP BY clause groups records into summary rows, often used with aggregate functions.

Examples:

Count Number of Students in Each City:

SELECT city, COUNT(name) FROM student GROUP BY city;

Expected Output:

Practice Exercises

Exercise 1

Write a query to find the average marks in each city in ascending order.

SELECT city, AVG(marks) FROM student GROUP BY city ORDER BY AVG(marks);

Exercise 2

Given a table with columns customer_id, customers, mode of payments, city. Find the total payment according to each payment method.

SELECT mode, COUNT(customer_id) FROM payment_table GROUP BY mode;

Exercise 3

Create a database named LibraryDB, create tables for books and authors, and insert sample data.

CREATE DATABASE LibraryDB;
USE LibraryDB;
CREATE TABLE authors (
  author_id INT PRIMARY KEY,
  author_name VARCHAR(100)
);
CREATE TABLE books (
  book_id INT PRIMARY KEY,
  title VARCHAR(100),
  author_id INT,
  FOREIGN KEY (author_id) REFERENCES authors(author_id)
);
INSERT INTO authors (author_id, author_name) VALUES (1, 'George Orwell'), (2, 'Harper Lee'), (3, 'F. Scott Fitzgerald');
INSERT INTO books (book_id, title, author_id) VALUES (1, '1984', 1), (2, 'To Kill a Mockingbird', 2), (3, 'The Great Gatsby', 3);
SELECT  FROM authors;
SELECT  FROM books;

Expected Output for authors Table

Expected Output for books Table

This concludes the second part of our SQL tutorial. In the next part, we will explore more advanced SQL topics and queries. Stay tuned!