Pre-requisites
1. Install PostgreSQL and psql-CLI to your system
If you haven't installed PostgreSQL and psql-CLI in your system, follow the steps in this link.
2. Create a psql role
I have created a role named fahid in the psql. Follow the instructions in the same link to create a role (preferably a role with your computer-name).
3. Access the Postgre prompt
Use either of these commands to access Postgre Prompt. Replace fahid with your_username.
psql
OR
psql -U fahid
Before we start, create a database sample in the psql shell.
4. Create an empty database sample
To create an empty database, run the following command in the postgre prompt. (Note: Do not miss the ;)
CREATE DATABASE sample;
To confirm whether the database sample is created, list the databases using the following command in the postgre shell.
\list
You should be able to see the database ipl in the list of databases with your username as the owner.
5. Connect to sample database
To connect to the database, run
\c sample
6. Copy the contents to the database
Make sure you download sample.sql to your computer.
Now to copy/import the contents to the database, run
\i /path/to/the/file/sample.sql
6. Display the tables in the database
Run this to display the tables in the database.
\dt
You will be able to see all available tables in the sample database.
SELECT STATEMENT
The most basic statement is SELECT statement. It allows us to retreive records from the table.
- To retreive all the columns:
SELECT * FROM customer;
- To specifically select one or more columns:
SELECT cust_code, cust_name, cust_city, working_area FROM customer;
WHERE CONDITION
To have a condition in your SELECT statement, we can use WHERE condition.
SELECT cust_code, cust_name, cust_city FROM customer
WHERE cust_city = 'London';
We will get the following output.
cust_code | cust_name | cust_city
-----------+-----------+-----------
C00013 | Holmes | London
C00024 | Cook | London
C00015 | Stuart | London
C00023 | Karl | London
AND and OR operators
We can add additional conditions and filter the table using AND or OR operators.
SELECT cust_code, cust_name, cust_city FROM customer
WHERE cust_city = 'London' OR cust_city = 'New York';
Selecting Grade 1 customers in India
SELECT cust_code, cust_name, cust_city, grade FROM customer
WHERE cust_country = 'India' AND grade = 1;
ORDER BY
To order the table by a specific column(s), we can use ORDER BY clause.
SELECT cust_code, cust_name, cust_city, payment_amt
FROM customer
ORDER BY payment_amt;
To arrange the result in descending order, we can use DESC keyword.
SELECT cust_code, cust_name, cust_city, payment_amt
FROM customer
ORDER BY payment_amt DESC;
DISTINCT
To remove duplicates and select the distinct elements use DISTINCT keyword.
SELECT DISTINCT cust_city
FROM customer;
BETWEEN
To select data within a range, we can use BETWEEN with AND.
SELECT cust_code, cust_name, payment_amt
FROM customer
WHERE payment_amt BETWEEN 6000 AND 8000;
LIKE
To match one or more characters, LIKE is used.
_ is used to match exactly one character and % is used to match zero or more characters.
Customers starting with the name 'Ra'
SELECT cust_code, cust_name, cust_city
FROM customer
WHERE cust_name LIKE 'Ra%';
COUNT
To count number of results, we can use COUNT
SELECT COUNT(cust_name)
FROM customer
WHERE cust_name LIKE 'Ra%';
OTHER AGGREGATE FUNCTIONS
| KEYWORD | USE |
|---|---|
| SUM | To calculate the sum |
| AVG | To calculate the average |
| MIN | To find the minimum of the column |
| MAX | To find the maximum of the column |
AS
We can use AS as alias in SQL
SELECT COUNT(cust_name) AS total
FROM customer
WHERE cust_name LIKE 'Ra%';
GROUP BY
GROUP BY is used to group rows that have the same values into summary rows
SELECT DISTINCT cust_country, COUNT(*) AS count
FROM customer
GROUP BY cust_country;
HAVING
To select countries having count greater than 4
SELECT DISTINCT cust_country, COUNT(*) AS count
FROM customer
GROUP BY cust_country
HAVING COUNT(*) > 4;
JOIN
A JOIN clause is used to combine rows from two or more tables.
ON keyword is used to specify the coommon columns
SUBQUERIES
We can use a query inside a query to further restrict the output data
SELECT * FROM customer
WHERE cust_code IN
(SELECT cust_code
FROM customer
WHERE payment_amt < 6000);
DELETE
To delete complete table
DELETE FROM customer;
We may also use other conditions to specifically delete rows.
DELETE FROM customer
WHERE cust_name = 'Steven';
DROP
To completely drop the table, we may use
DELETE TABLE customer;
Conclusion
In the next Episode of this Series, we will discuss few Database Theoretical Concepts.
Top comments (0)