SQL Course (Part-2)

-


CREATING DATABASE AND TABLES:

DATA TYPES:

While creating database and tables, you should be carefully consider which data types should be used for the data to be stored.

Types of data types are:

1.     Boolean --- True or False

2.     Character --- char, varchar and text

3.     Numeric --- integer and floating-point number

4.     Temporal --- date, time, timestamp and interval

5.     UUID --- Universal unique identifier’s

6.     Array --- Stores an array of string, numbers, etc..

7.     JSON

8.     Hstore Key --- value pair

9.     Special types such as network address and geometric data


PRIMARY AND FOREIGN KEY:

A primary key is a column or a group of columns used to identify a row uniquely in a table, it is also allow us to easily discern what columns should be used for joining tables together.

A foreign key is a field or group of fields in a table that uniquely identifies a row in another table.

A foreign key is defined in a table that references to the primary key of the other table. The table that contains foreign key is called referencing table and the table to which foreign key references is called referenced table, a table can have multiple foreign key depending on its relationship with other tables.


CONSTRAINTS:

Constraints are the rules enforced on data columns on table, these are used to prevent invalid data from being entered into the database and ensures accuracy and reliablity of the data in the database.


Constraints are divided into two categories:

1.     Column Constraints --- The data in the column to adhere to certain conditions.

2.     Table Constraints --- Applied to the entire table rather than to an individual column.


Most commonly used constraints:

1.     NOT NULL --- Column cannot have NULL value.

2.     UNIQUE --- All values in the column are different.

3.     PRIMARY KEY

4.     FOREIGN KEY

5.     CHECK --- Ensures all the values in a column satisfy certain conditions.

6.     EXCLUSION --- Ensures that if any two row are compared on the specified column or expression using the specified operator, not all of these comparisons  will return TRUE.

7.     REFERENCES --- to constrain the values stored in the column that must exist in a column in another table.

CREATE TABLE:

SERIAL will create a sequence object and set the next value generated by the sequence as the default value for the column, it is perfect for primary key, because it logs unique integer entries for you automatically upon insertion.


Syntax:

CREATE TABLE table_name(column_name TYPE column_constraint,

     column_name TYPE column_constraint,

     table_constraint table_constraint)

INHERITS existing_table_name;

 

CREATE TABLE account(

            user_id SERIAL PRIMARY KEY,

            username VARCHAR(50) UNIQUE NOT NULL,

            password VARCHAR(50) NOT NULL,

            email VARCHAR(100) UNIQUE NOT NULL,

            created_on TIMESTAMP NOT NULL,

            last_login TIMESTAMP

);

 

CREATE TABLE job(

            job_id SERIAL PRIMARY KEY,

            job_name VARCHAR(100) UNIQUE NOT NULL

);

 

CREATE TABLE account_job(

            user_id INT REFERENCES account(user_id),

            job_id INT REFERENCES job(job_id),

            hire_date TIMESTAMP

);

 

user_id INT REFERENCES account(user_id) --- creating a foreign key


INSERT:

INSERT allow you to add rows to a table.

SERIAL columns do not need to provided a value.


Syntax:

INSERT INTO table_name(column1, column2,…)

VALUES (value1,value2),

                (value1,value2),…. ;

 

INSERT INTO account (username,password,email,created_on)

VALUES

('Anubhav','password1','aryan@gmail.com',CURRENT_TIMESTAMP)

('Ankur','password2','ankuri@gmail.com',CURRENT_TIMESTAMP),

('Abdul','password3','abdulla@gmail.com',CURRENT_TIMESTAMP),

('Tanuj','password4','tanu@gmail.com',CURRENT_TIMESTAMP);;

SELECT * FROM account;

INSERT INTO job(job_name)

VALUES

('Astronaut'),('Developer'),('President');

SELECT * FROM job;

 

INSERT INTO account_job(user_id,job_id,hire_date)

VALUES

(1,1,CURRENT_TIMESTAMP);

SELECT * FROM account_job;

 

INSERT INTO account_job(user_id,job_id,hire_date)

VALUES

(10,10,CURRENT_TIMESTAMP);

SELECT * FROM account_job;

ERROR: insert or update on table "account_job" violates foreign key constraint "account_job_user_id_fkey" DETAIL: Key (user_id)=(10) is not present in table "account".

SQL state: 23503


UPDATE:

Syntax:

UPDATE table_name

SET column1 = value1

      column2 = value2

WHERE condition;


Update Join:

UPDATE table_A

SET original_column = table_B.new_column

FROM table_B

WHERE condition;


Return affected rows:

UPDATE table_name

SET column1 = column2

RETURNING column1,column2,..;


UPDATE account

SET last_login = created_on;

SELECT * FROM account;

 

UPDATE account_job

SET hire_date = account.created_on

FROM account

WHERE account_job.user_id  =  account.user_id;

SELECT * FROM account_job;


UPDATE account

SET last_login = CURRENT_TIMESTAMP

RETURNING email, created_on, last_login;

 

DELETE:

DELETE clause to remove rows from table.


Syntax:

DELETE FROM table_name

WHERE column_name = value;


Delete rows based on their presence in other table

DELET FROM table_A

USING table_B

WHERE table_A.id = table_B.id;

 

Delete all rows from table

DELETE FROM table_name;

 

ALTER:

Alter clause allows for the change to an existing table structure, such as:

1. Adding, dropping or renaming columns

2. Changing a column data type

3. Set default calues for a column

4. Add CHECK constraints

5. Rename table


Syntax:

CREATE TABLE information(

            info_id SERIAL PRIMARY KEY,

            title VARCHAR(50) NOT NULL,

            person VARCHAR(50) UNIQUE NOT NULL

);

SELECT * FROM information;

 

ALTER TABLE information

RENAME TO new_info;

SELECT * FROM new_info;

 

ALTER TABLE new_info

RENAME COLUMN person TO people;

SELECT * FROM new_info;


INSERT INTO new_info(title)

VALUES

('some title');                          #shows error since people column cannot be NULL

ALTER TABLE new_info

ALTER COLUMN people DROP NOT NULL;

SELECT * FROM new_info;

 

DROP:

DROP allows for the complete removal of a column in a table, however it will not remove columns used in views, triggers or stored procedures without the additional CASCADE clause.


Syntax:

ALTER TABLE new_info

DROP COLUMN IF EXISTS people;

SELECT * FROM new_info;


CHECK:

CHECK constraint allows us to create more customized constraints that adhere to a certain condition, such as making sure all inserted integer values fall below a certain threshold.


Syntax:

CREATE TABLE employees(

            emp_id SERIAL PRIMARY KEY,

            first_name VARCHAR(50) NOT NULL,

            last_name VARCHAR(50) NOT NULL,

            birth_date DATE CHECK(birth_date > '1900-01-01'),

            hire_date DATE CHECK (hire_date > birth_date),

            salary INT CHECK (salary > 0)

);


INSERT INTO employees(first_name,last_name,birth_date,hire_date,salary)

VALUES

('Anu','Suman','1999-09-19','2012-05-13',50000);

SELECT * FROM employees;


CONDITIONAL EXPRESSIONS AND PROCEDURES:

CASE:

CASE statement is used to execute SQL code when certain conditions are met, similar if/else statements in other programming language.


General Syntax:

CASE

WHEN condition_1 THEN result_1

WHEN condition_2 THEN result_2

ELSE some_other_result

END


SELECT customer_id,

CASE

            WHEN (customer_id <= 100) THEN 'Premium'

            WHEN (customer_id BETWEEN 100 AND 200) THEN 'Plus'

            ELSE 'Normal'

END AS Membership

FROM customer

ORDER BY customer_id;


Case expression syntax:

CASE expression

WHEN value_1 THEN result_1

WHEN value_2 THEN result_2

ELSE some_other_result

END

 

SELECT customer_id,

CASE customer_id

            WHEN 2 THEN 'Winner'

            WHEN 5 THEN 'Second'

            ELSE 'Normal'

END AS raffle_result

FROM customer

ORDER BY customer_id;

 

SELECT

SUM(CASE rental_rate

            WHEN 0.99 THEN 1

            ELSE 0

END) AS num_0.99,

SUM(CASE rental_rate

            WHEN 2.99 THEN 1

            ELSE 0

END) AS num_2.99

FROM film;


COALESCE:

COALESCE function accepts an unlimited number of arguments. It returns the first argument that is not null. If all arguments are null, the COALESCE function will return null.

This function is useful when querying a table that contains NULL values and substituting it with another value.








CAST:

CAST operator let's you convert from one data type to another.

General Syntax:

SELECT CAST('5' AS INTEGER)

 

SELECT CHAR_LENGTH(CAST(inventory_id AS VARCHAR)) FROM rental;


postgreSQL syntax:

SELECT '5' :: INTEGER

SELECT '10' :: INTEGER;


NULLIF:

NULLIF function takes 2 inputs and return NULL if both are equal, otherwise it return the first argument passed.

 

CREATE TABLE depts(

            first_name VARCHAR(50),

            department VARCHAR(50)

);

 

INSERT INTO depts(first_name,department)

VALUES

('Anu','A'),

('Anku','A'),

('Abby','B');

SELECT * FROM depts;

 

SELECT(

SUM(CASE WHEN department = 'A' THEN 1 ELSE 0 END) /

SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END)

)

FROM depts;

 

DELETE FROM depts

WHERE department = 'B';


SELECT(

SUM(CASE WHEN department = 'A' THEN 1 ELSE 0 END) /

NULLIF(SUM(CASE WHEN department = 'B' THEN 1 ELSE 0 END),0)

)

FROM depts;

returns null

 

VIEWS:

Instead of having to perform the same query over and over again as a starting point, you can create a VIEW to quickly see this query with a simple call.

A view is a database object that is of a stored query, notice that a view does not store data physically, it simply stores the query. You can alter and update a existing table.

 

Syntax:

CREATE VIEW customer_info AS

SELECT first_name,last_name,address FROM customer

INNER JOIN address

ON customer.address_id =  address.address_id;

 

SELECT * FROM customer_info;

 

To add new column:

CREATE OR REPLACE VIEW customer_info AS

SELECT first_name,last_name,address,district FROM customer

INNER JOIN address

ON customer.address_id =  address.address_id;

 

SELECT * FROM customer_info;

 

To drop and rename:

DROP VIEW IF EXISTS customer_info;

ALTER VIEW customer_info RENAME TO c_info;

 

IMPORT AND EXPORT:

It help us to import data from a .csv file to an already existing file.

Note:

1.     Not every outside data file will work, variations in formatting, macros, data types etc. may prevent the import command from reading the file, at which point, you must edit your file to be compatible with SQL.

2.     The import command does not create table for you.

 Importing a .csv file, similar for export:











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

                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                  

Location: Jamalpur, Bihar, India

Comments

Post a Comment

Popular posts from this blog

SQL Course (PART-1)

-
Image
First of all I want to say that all the queries than has been written and the table on which different queries has been executed are done on a table that has already been uploaded in the "postgreSQL". Later on we will see all the queries that how to create and manipulate the tables according to the user needs.    SELECT Statement:      SELECT is the most common statement used, and it allows us to retrieve information from the table. * is used to query everything, which increases the traffic between the database server and application and also slows down the retrieval of results. Syntax : SELECT column_name1,.... FROM table_name; SELECT * FROM city; SELECT DISTINCT: DISTINCT is used to query the unique data from the table, will not show the duplicate values.   Syntax : SELECT DISTINCT (column_name1,column_name2,…) FROM table_name; SELECT DISTINCT (release_year, rental_rate) FROM film;   COUNT : COUNT() func. returns the number o...
Read more

PYTHON BASICS OF BEGINNER's (PART-2)

-
Image
MATH FUNCTION: 1. We have a math module in python that will help us to access different type of math functions.    To access and try all the math function check out this link: https://docs.python.org/3/library/math.html   import math   x=2.9 print(round(x)) print(abs(-2.9)) print(math.ceil(2.9)) print(math.floor(2.9)) print(math.remainder(4,3))   3 2.9 3 2 1.0   NESTED LOOPS: 1. for x in range(4):     for y in range(3):         print("The number is {} {}".format(x,y))         print(f'Our co-ordinate is ({x},{y})')   The number is 0 0 Our co-ordinate is (0,0) The number is 0 1 Our co-ordinate is (0,1) The number is 0 2 Our co-ordinate is (0,2) The number is 1 0 Our co-ordinate is (1,0) The number is 1 1 Our co-ordinate is (1,1) The number is 1 2 Our co-ordinate is (1,2) The number is 2 0 Our c...
Read more

Open_CV BASICS

-
Image
  OpenCV is a library of programming functions mainly aimed at real-time computer vision. 1.    HOW TO READ AN IMAGE: OpenCv is a image processing and computer vision library that was made by INTEL. We do not have to treat a image like an image but we will treat it as a MATRIX. Always save the image in that folder where you are creating your python project otherwise you have to give the whole location of that image. import cv2 img = cv2.imread( 'img1.jpg' ) cv2.imshow( 'Output Image' , img) cv2.waitKey( 0 ) cv2.destroyAllWindows() #cv2 is class inside cv2 lib #imshow()function is used to show image #waitkey()function used to hold the img untill a key is pressed #destroyAllWindows function is used as soon as we hit any key it will destroy all windows   2.    HOW TO WRITE AN IMAGE: import cv2 img = cv2.imread( 'img2.jpg' ) cv2.imshow( 'Original image' , img) cv2.imwrite( 'Output.jpg' , img) cv2.imwrite( 'Output.png...
Read more