[IBM]Databases and SQL for Data Science with Python- Week 2 & Week 3

Relational DB - Data independence 

 

DDL: Defining objects(Table)- define, change, Drop

Create, alter, truncate, drop

 

DML: Manipulating data in tables - modify, CRUD operations(Create, Remove, Update and Delete)

insert, select, update, delete

 

Create Table Statement - includes a definition of attributes in relational DB

CREATE TABLE table_name ( Column_name datatype optional parameter, ...)

CREATE TABLE provinces ( id char(2) PRIMARY KEY NOT NULL, name varchar(24))

CREATE TABLE author ( author_ID CHAR(2) PRIMARY KEY NOT NULL.,

                                        lastname ... ) 

 

ALTER, DROP, and TRUNCATE table 

ALTER: Add, remove columns, modify the data type of column, add or remove keys/constraints

ALTER TABLE author ALTER COLUMN telephone_number SET DATA TYPE CHAR(20); 

ALTER TABLE author DROP COLUMN telephone_number;

DROP TABLE author;  -> to remove the table from the DB 

TRUNCATE TABLE author IMMEDIATE; -> Drop all rows in the table 

 

CREATE TABLE TEST (ID integer, NAME varchar(30)); 

CREATE TABLE COUNTRY ( ID int, CCODE char(2), NAME varchar(60));

CREATE TABLE COUNTRY ( ID int NOT NULL, CCODE char(2), NAME varchar(60), PRIMARY KEY(ID));

DROP TABLE COUNTRY; 

CREATE TABLE COUNTRY ( ID interger PRIMARY KEY NOT NULL, CCODE char(2), NAME varchar(60)); 

 

 

Distinct clause to eliminate the duplicates.

Group by clause 

Having clause - restrict the result set, combined with "group by" clause

 

Built-in DB

- reduce the amount of data that needs to be retrieved, speed up data processing

Aggregate functions 

- sum, min, max, avg 

SCALAR

- round, length, ucase, lcase (Upper case and lower case)

- where clause can be used 

- Distinct () : select Dcitinct(ucase(animal)) from petrescue;

 

Date and Time built-in Functions 

- DATE : YYYYMMDD 

- TIME : HHMMSS

- TIMESTAMP : YYYYXXDDHHMMSSZZZZZZ(micro second)

- DATE/TIME functions : year, month, day, etc. 

- WHERE clause : where MONTH(RESCUE DAY)=5 

- ARITHMETIC : RESCUEDATE + 3 DAYS 

- SPECIAL REGISTERS: result -  YMMDD 

 

SUB_QUERIES and NESTED Selects

- Cannot evaluate Aggregate functions like AVG() in the WHERE clause: use a sub-Select Expression

- Select EMP_ID, F_NAME, SALARY from EMPLOYEES where SALARY < (select AVG(SALARY) from EMPLOYEES);

- Column Expressions : 

- Derived Tables or Table Expressions : working with multiple tables and doing join 

 : select * from (select EMP_ID, F_NAME, L_NAME, DEP_ID, from employees) AS EMP4ALL; 

 

Working with Multiple Tables 

- sub-queries 

- implicit join ;  full join(Cartesian join) : every row will be joined->  select * from employees, departments; 

www.sqltutorial.org/sql-cheat-sheet/