Skip to content

SQL

Jump to bottom
nj2208 edited this page Jan 1, 2023 · 7 revisions

Database Management Systems

A software program that helps to create , read , update and delete information in a database.

Types of Database Systems

  1. Relational DB ( SQL) - example mariaDB , Oracle , MySQL , postgres

    • Stores information in tables in forms of rows and columns
    • Use Structured Query Language for interaction with Database

  2. Non Relational DB ( noSQL ) - Redis , MongoDB , Document DB

    • Stores information in the form, of key value pair like in Redis or json as in MongoDB or graph format as in neoj
    • No standard language like SQL for relational DB

  3. Primary Key uniquely identifies the row in a table.

  4. Surrogate PK does not have validity in real world and applicable only w.r.t table like auto generated id

  5. Real PK has validity in real world like email id or ssn number

  6. foreign KEY FK identifies the PK of another table in a database and establish the relation ship between tables

  7. Composite PK consists of two or more columns uniquely represent PK

  8. SQL is a hybrid language to interact with the database and consists of below

    1. Data Definition Language - for defining data base schemas
    2. Data Query Language - for querying the information already stored in the database
    3. Data Control Language - To control access to the data in the database ( user and permissions management)
    4. Data Manipulation Language - to insert , delete or update the data in a database

  9. User details

    master - root postgres - root

SQL Samples:-

DROP TABLE student; CREATE TABLE student ( id SERIAL , name VARCHAR(20) NOT NULL , major VARCHAR(20) DEFAULT 'undecided', PRIMARY KEY(id) );

ALTER TABLE student ADD gpa DECIMAL(3,2); ALTER TABLE student DROP COLUMN gpa; SELECT COLUMN_NAME, DATA_TYPE, CHARACTER_MAXIMUM_LENGTH from INFORMATION_SCHEMA.COLUMNS where table_name = 'student';

INSERT INTO student(name,major) VALUES('Jack' , 'Biology'); INSERT INTO student(name,major) VALUES('Nitin','Comp Sci'); INSERT INTO student(name,major) VALUES('Jack' , 'Humanity'); INSERT INTO student(name,major) VALUES('Kate' , 'Sociology'); UPDATE TABLE student (name,major) VALUES('Kate') where id = 3; SELECT * FROM student ORDER BY id DESC; SELECT * FROM student where major = 'undecided' OR major = 'Biology';

UPDATE student

SET major = 'comp sci'

WHERE name = 'Nitin';

DELETE FROM student

where name = 'Nitin' AND major = 'comp sci';

UPDATE student

SET major = 'Physics'

WHERE id = 1;

SELECT * FROM student WHERE name IN ('Nitin');

CREATE TABLE employee ( emp_id SERIAL , first_name VARCHAR(20) NOT NULL , last_name VARCHAR(20) NOT NULL , birth_date DATE, sex VARCHAR(1), salary INT, super_id INT, branch_id INT, PRIMARY KEY(emp_id) );

CREATE TABLE branch ( branch_id SERIAL , branch_name VARCHAR(40) NOT NULL , mgr_id INT, mgr_start_date DATE, PRIMARY KEY(branch_id), FOREIGN KEY(mgr_id) REFERENCES employee(emp_id) ON DELETE SET NULL );

ALTER TABLE employee ADD FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL;

ALTER TABLE employee ADD FOREIGN KEY(super_id) REFERENCES employee(emp_id) ON DELETE SET NULL;

CREATE TABLE client ( client_id SERIAL , client_name VARCHAR(20) NOT NULL , branch_id INT, PRIMARY KEY(client_id), FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE SET NULL );

CREATE TABLE works_with ( emp_id INT , client_id INT , total_sales INT, PRIMARY KEY(emp_id,client_id), FOREIGN KEY(emp_id) REFERENCES employee(emp_id) ON DELETE CASCADE, FOREIGN KEY(client_id) REFERENCES client(client_id) ON DELETE CASCADE );

CREATE TABLE branch_supplier ( branch_id INT , supplier_name VARCHAR, supply_type VARCHAR(100), PRIMARY KEY (branch_id,supplier_name), FOREIGN KEY(branch_id) REFERENCES branch(branch_id) ON DELETE CASCADE

);

------------------------------------------------------------------------------

-- Corporate INSERT INTO employee VALUES(100, 'David', 'Wallace', '1967-11-17', 'M', 250000, NULL, NULL);

INSERT INTO branch VALUES(1, 'Corporate', 100, '2006-02-09');

UPDATE employee SET branch_id = 1 WHERE emp_id = 100;

INSERT INTO employee VALUES(101, 'Jan', 'Levinson', '1961-05-11', 'F', 110000, 100, 1);

-- Scranton INSERT INTO employee VALUES(102, 'Michael', 'Scott', '1964-03-15', 'M', 75000, 100, NULL);

INSERT INTO branch VALUES(2, 'Scranton', 102, '1992-04-06');

UPDATE employee SET branch_id = 2 WHERE emp_id = 102;

INSERT INTO employee VALUES(103, 'Angela', 'Martin', '1971-06-25', 'F', 63000, 102, 2); INSERT INTO employee VALUES(104, 'Kelly', 'Kapoor', '1980-02-05', 'F', 55000, 102, 2); INSERT INTO employee VALUES(105, 'Stanley', 'Hudson', '1958-02-19', 'M', 69000, 102, 2);

-- Stamford INSERT INTO employee VALUES(106, 'Josh', 'Porter', '1969-09-05', 'M', 78000, 100, NULL);

INSERT INTO branch VALUES(3, 'Stamford', 106, '1998-02-13');

UPDATE employee SET branch_id = 3 WHERE emp_id = 106;

INSERT INTO employee VALUES(107, 'Andy', 'Bernard', '1973-07-22', 'M', 65000, 106, 3); INSERT INTO employee VALUES(108, 'Jim', 'Halpert', '1978-10-01', 'M', 71000, 106, 3);

-- BRANCH SUPPLIER INSERT INTO branch_supplier VALUES(2, 'Hammer Mill', 'Paper'); INSERT INTO branch_supplier VALUES(2, 'Uni-ball', 'Writing Utensils'); INSERT INTO branch_supplier VALUES(3, 'Patriot Paper', 'Paper'); INSERT INTO branch_supplier VALUES(2, 'J.T. Forms & Labels', 'Custom Forms'); INSERT INTO branch_supplier VALUES(3, 'Uni-ball', 'Writing Utensils'); INSERT INTO branch_supplier VALUES(3, 'Hammer Mill', 'Paper'); INSERT INTO branch_supplier VALUES(3, 'Stamford Lables', 'Custom Forms');

-- CLIENT INSERT INTO client VALUES(400, 'Dunmore Highschool', 2); INSERT INTO client VALUES(401, 'Lackawana Country', 2); INSERT INTO client VALUES(402, 'FedEx', 3); INSERT INTO client VALUES(403, 'John Daly Law, LLC', 3); INSERT INTO client VALUES(404, 'Scranton Whitepages', 2); INSERT INTO client VALUES(405, 'Times Newspaper', 3); INSERT INTO client VALUES(406, 'FedEx', 2);

-- WORKS_WITH INSERT INTO works_with VALUES(105, 400, 55000); INSERT INTO works_with VALUES(102, 401, 267000); INSERT INTO works_with VALUES(108, 402, 22500); INSERT INTO works_with VALUES(107, 403, 5000); INSERT INTO works_with VALUES(108, 403, 12000); INSERT INTO works_with VALUES(105, 404, 33000); INSERT INTO works_with VALUES(107, 405, 26000); INSERT INTO works_with VALUES(102, 406, 15000); INSERT INTO works_with VALUES(105, 406, 130000);

SELECT * FROM employee LIMIT 5 ; SELECT * FROM employee where first_name LIKE '_a'; SELECT salary FROM employee UNION SELECT total_sales FROM works_with;

SELECT * from branch_supplier; SELECT * FROM branch; SELECT * FROM employee; SELECT * FROM works_with; INSERT INTO branch VALUES(4,'Buffalo',NULL,NULL);

SELECT employee.first_name , employee.salary , branch.branch_name FROM employee JOIN branch ON employee.emp_id = branch.mgr_id;

SELECT employee.first_name , employee.last_name FROM employee WHERE employee.emp_id IN ( SELECT works_with.emp_id FROM works_with WHERE works_with.total_sales > 30000 );

SELECT works_with.emp_id FROM works_with WHERE works_with.total_sales > 30000;

Reference Links:-

  1. https://www.youtube.com/watch?v=HXV3zeQKqGY
Clone this wiki locally