روز 38 – کلید اصلی ، کلید خارجی در بانک اطلاعاتی ، انواع زبانهای SQL

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

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

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

movie=# create table customers(customer_id int GENERATED ALWAYS AS IDENTITY, customer_name text not null, primary key(customer_id));
CREATE 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)

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

movie=# insert into contacts(customer_id, contact_name, mobile, email) values (1,'Guru', 1234, 'guru@guru.com'), (2, 'Pritha', 2345, 'lakshmi@pritha.com');
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 | guru@guru.com
          2 |           2 | Pritha       |   2345 | lakshmi@pritha.com
(2 rows)

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 | lakshmi@pritha.com
(1 row)

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

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

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

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

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)

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)

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

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

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)

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)

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)

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)

movie=# truncate table emp;
TRUNCATE TABLE

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

movie=# drop table emp;
DROP TABLE

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

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)

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)

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)

movie=# drop table if exists emp;
NOTICE:  table "emp" does not exist, skipping
DROP 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)

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).