Day 38 - Primary key, Foreign key in Database, Types of SQL Languages

Primary key:

A primary key is a column (or a set of columns) in a database table that uniquely identifies each record in that table.

*Must be unique (no duplicate values).
*cannot have NULL values.
*Each table can have only one primary key.

Create a Table for Storing Book Information using primary key:

movie=# create table book 
(book_id integer PRIMARY KEY, name text, author text);
CREATE TABLE

Create a Table for Citizens with Aadhar and PAN Information as a primary key:

movie=# create table citizens(aadhar_no integer, pan_no text, name text, primary key(aadhar_no, pan_no));
CREATE TABLE

Insert a New Citizen Record into the Citizens Table:

movie=# insert into citizens values(123412,'g1234t','pritha');
INSERT 0 1

GENERATED ALWAYS AS IDENTITY:

The GENERATED ALWAYS AS IDENTITY clause is used to create an auto-incrementing column in SQL, which automatically generates a unique value for the column whenever a new row is inserted into the table.

Create a Customers Table with Auto-Incrementing Customer ID:

movie=# create table customers(customer_id int GENERATED ALWAYS AS IDENTITY, customer_name text not null, primary key(customer_id));
CREATE TABLE

Insert Multiple Customer Records into the Customers Table:

movie=# insert into customers(customer_name) values('guru'),('pritha'),('pritha'),('muthu');
INSERT 0 4

movie=# select * from customers;
 customer_id | customer_name 
-------------+---------------
           1 | guru
           2 | pritha
           3 | pritha
           4 | muthu
(4 rows)

Foreign key:

A Foreign Key is a column or a set of columns that establishes a relationship between two tables by referencing the Primary Key of another table.

Create a Contacts Table Linked to Customers:

movie=# CREATE TABLE contacts
(contact_id int GENERATED ALWAYS AS IDENTITY, 
customer_id int, 
contact_name text not null, 
mobile int, 
email text, 
PRIMARY KEY (contact_id), 
CONSTRAINT fk_customer  FOREIGN KEY(customer_id) REFERENCES customers(CUSTOMER_ID) 
on DELETE CASCADE
);
CREATE TABLE

Foreign Key (customer_id): Links each contact to a specific customer from the customers table. This establishes a relationship between the two tables.

ON DELETE CASCADE:If a record in the customers table is deleted, all corresponding records in the contacts table will also be deleted automatically.

Insert Contact Records Linked to Customers:

movie=# insert into contacts(customer_id, contact_name, mobile, email) values (1,'Guru', 1234, '[e-mail]'), (2, 'Pritha', 2345, '[e-mail]');
INSERT 0 2

movie=# select * from customers;
 customer_id | customer_name 
-------------+---------------
           1 | guru
           2 | pritha
           3 | pritha
           4 | muthu
(4 rows)

movie=# select * from contacts;
 contact_id | customer_id | contact_name | mobile |       email        
------------+-------------+--------------+--------+--------------------
          1 |           1 | Guru         |   1234 | [e-mail]
          2 |           2 | Pritha       |   2345 | [e-mail]
(2 rows)

Delete a Customer Record and Associated Contacts:

movie=# delete from customers where customer_id=1;
DELETE 1

movie=# select * from contacts;
 contact_id | customer_id | contact_name | mobile |       email        
------------+-------------+--------------+--------+--------------------
          2 |           2 | Pritha       |   2345 | [e-mail]
(1 row)

Create a Students Table with Auto-Incrementing Student ID:

movie=# create table students_2
(student_id SERIAL primary key, 
name varchar(20) not null, 
class int);
CREATE TABLE

SERIAL: The student_id column is set as a SERIAL data type, which automatically increments with each new row. It behaves like an auto-incrementing integer column.

Create a Courses Table with a Foreign Key to Students:

movie=# create table courses
(course_id SERIAL primary key, 
student_id int, 
course_name text, 
FOREIGN KEY (student_id) REFERENCES students_2(student_id) on DELETE CASCADE);
CREATE TABLE

Insert Multiple Student Records into the Students Table:

movie=# insert into students_2 values(101,'guru',4),(102,'pritha',5),(103,'kuhan',3);
INSERT 0 3

Insert Multiple course Records into the courses Table:

movie=# insert into courses(student_id, course_name) values(101, 'Karate'), (102,'Kung fu'), (103,'Yoga');
INSERT 0 3

Delete a Student Record from the Students Table:

movie=# delete from students_2 where student_id = 102;
DELETE 1

movie=# select * from courses;
 course_id | student_id | course_name 
-----------+------------+-------------
         4 |        101 | Karate
         6 |        103 | Yoga
(2 rows)

Delete a course Record from the courses Table:

movie=# delete from courses where course_id = 4;
DELETE 1

movie=# select * from courses;
 course_id | student_id | course_name 
-----------+------------+-------------
         4 |        101 | Karate
         6 |        103 | Yoga
(2 rows)

Create an Employee Table with Employee Details:

movie=# create table emp(empno numeric, empname text, joining_date date, dept_no numeric);
CREATE TABLE

Insert Employee Record into the Employee Table:

movie=# insert into emp values(101, 'Raja', '26-Feb-2025', 1);
INSERT 0 1

Add a Salary Column to the Employee Table:

movie=# alter table emp add salary int;
ALTER TABLE

movie=# select * from emp;
 empno | empname | joining_date | dept_no | salary 
-------+---------+--------------+---------+--------
   101 | Raja    | 2025-02-26   |       1 |       
(1 row)

Drop the Department Number Column from the Employee Table:

movie=# alter table emp drop column dept_no;
ALTER TABLE

movie=# select * from emp;
 empno | empname | joining_date | salary 
-------+---------+--------------+--------
   101 | Raja    | 2025-02-26   |       
(1 row)

Alter Column Data Type for Employee Name in the Employee Table:

movie=# alter table emp alter column empname TYPE varchar(30);
ALTER TABLE

movie=# select * from emp;
 empno | empname | joining_date | salary 
-------+---------+--------------+--------
   101 | Raja    | 2025-02-26   |       
(1 row)

Update Employee Salary in the Employee Table:

movie=# update emp set salary = 25000 where empno = 101;
UPDATE 1

movie=# select * from emp;
 empno | empname | joining_date | salary 
-------+---------+--------------+--------
   101 | Raja    | 2025-02-26   |  25000
(1 row)

Remove All Records from the Employee Table:

movie=# truncate table emp;
TRUNCATE TABLE

movie=# select * from emp;
 empno | empname | joining_date | salary 
-------+---------+--------------+--------
(0 rows)

Drop the Employee Table from the Database:

movie=# drop table emp;
DROP TABLE

movie=# select * from emp;
ERROR:  relation "emp" does not exist
LINE 1: select * from emp;
                      ^

view:

A view in SQL is a virtual table based on the result of a query. It contains rows and columns just like a real table, but it does not store data physically.

Instead, the data is dynamically generated when the view is accessed, based on the query it is defined with.

movie=# create view emp_view as 
select * from student_2;
CREATE VIEW

movie=# select * from student_2;
 student_id | name | class  
------------+------+--------
        101 | abcd |  23132
        102 | sds  |  32443
        103 | fff  | 234334
(3 rows)

movie=# drop view emp_view;
DROP VIEW

movie=# select * from student_2;
 student_id | name | class  
------------+------+--------
        101 | abcd |  23132
        102 | sds  |  32443
        103 | fff  | 234334
(3 rows)

explain:

The EXPLAIN statement in SQL is used to obtain information about how the database engine executes a query.

It provides a detailed breakdown of the execution plan, including how tables are accessed, which indexes are used, and the order of operations performed by the query planner.

movie=# explain select * from student_2;
                         QUERY PLAN                          
-------------------------------------------------------------
 Seq Scan on student_2  (cost=0.00..18.60 rows=860 width=66)
(1 row)

movie=# explain select * from student_2 where name='abcd';
                        QUERY PLAN                         
-----------------------------------------------------------
 Seq Scan on student_2  (cost=0.00..20.75 rows=4 width=66)
   Filter: ((name)::text = 'abcd'::text)
(2 rows)

Index:

An index in SQL is a database object that improves the speed of data retrieval operations on a table at the cost of additional space and maintenance overhead.

movie=# create index name on student_2(name);
CREATE INDEX

movie=# explain select * from student_2 where name='abcd';
                        QUERY PLAN                        
----------------------------------------------------------
 Seq Scan on student_2  (cost=0.00..1.04 rows=1 width=66)
   Filter: ((name)::text = 'abcd'::text)
(2 rows)

Drop the emp Table if It Exists:

movie=# drop table if exists emp;
NOTICE:  table "emp" does not exist, skipping
DROP TABLE

Rename Column in the student_2 Table:

movie=# alter table student_2 rename column name to Student_name;
ALTER TABLE

movie=# select * from student_2;
 student_id | student_name | class  
------------+--------------+--------
        101 | abcd         |  23132
        102 | sds          |  32443
        103 | fff          | 234334
(3 rows)

Add a CHECK Constraint on the class Column in student_2 Table:

movie=# alter table student_2 add check(class in (23132,32443,234334));
ALTER TABLE

movie=# insert into student_2 values(104,'frferf',23132);
INSERT 0 1

movie=# insert into student_2 values(105,'rferf',3423132);
ERROR:  new row for relation "student_2" violates check constraint "student_2_class_check"
DETAIL:  Failing row contains (105, rferf, 3423132).

SQL:

SQL (Structured Query Language) is a powerful language used to interact with relational databases.

It can be categorized into different types of languages based on the operations and tasks they perform.

1. Data Definition Language (DDL)

DDL is used to define and manage the structure of database objects, such as tables, indexes, and views.

CREATE: Used to create database objects (tables, indexes, views, etc.).
ALTER: Used to modify an existing database object (e.g., adding, deleting, or modifying columns).
DROP: Used to delete a database object (e.g., table, view).
TRUNCATE: Removes all rows from a table without logging individual row deletions.

2. Data Manipulation Language (DML)

DML is used for manipulating the data stored in the database. It includes operations like inserting, updating, deleting, and retrieving data.

SELECT: Used to retrieve data from one or more tables.
INSERT: Used to add new rows to a table.
UPDATE: Used to modify existing data within a table.
DELETE: Used to remove rows from a table.

3. Data Control Language (DCL)

DCL is used to control access to data in the database. It defines permissions for users and roles.

GRANT: Used to assign privileges to users or roles.
REVOKE: Used to remove privileges from users or roles.

4. Transaction Control Language (TCL)

TCL is used to manage transactions in a database, which are groups of DML operations. It ensures data integrity and consistency.

COMMIT: Saves all changes made during the current transaction.
ROLLBACK: Undoes changes made during the current transaction.
SAVEPOINT: Sets a point within a transaction to which you can roll back.
SET TRANSACTION: Used to configure the transaction properties.

5. Data Query Language (DQL)

DQL is primarily concerned with querying and retrieving data from the database.

SELECT: Used to retrieve data from one or more tables (this command is also part of DML but mainly falls under DQL because it's used for querying data).