learnSQL/1_querying_data.md at main · kishankumar07/learnSQL

First take terminal and type : [ psql -U ] , => will ask password => enter the password.
[ \l ] will show all the databases existing.
[ ! cls ] = will clear the terminal
[ \c ]

------------- To create a dataBase -----------------------

[ CREATE DATABASE ; ] = will create a table in the selected database. ---> { do not avoid the ; }
[ \c ] = will enter to that dataBase
[ \d ] - when inside table , \d will display all tables , \l has no effect

-[ \d ] - show only that particular table;

-[DROP DATABASE ] = Delete the database;

------------- To create a TABLE no_constraint ----------------------- CREATE TABLE ( id INT, name VARCHAR(50), email VARCHAR(50), date_of_birth DATE);

{  this will create a table with name <tableName> };
{ To see only this table from the list of all tables inside the database created : \d <tableName> }

------------------------------------- end -------------------------------

----------- To delete a TABLE-------------

[ DROP TABLE ; ] --> don't forget the ;

-------------------- CREATE A TABLE with Constranits -----------------------------

    CREATE TABLE employees(
        id BIGSERIAL NOT NULL PRIMARY KEY,
        name VARCHAR(50)  NOT NULL,
        email VARCHAR(50) NOT NULL UNIQUE,
        date_of_birth DATE
    );

done deal

---&&&&&&&&&**&&&&&&&&&&&&&&&&&&----------------------------

                SELECT

/Once inserted all entries to fill in the table, if you want to see it :

            SELECT * FROM employees;

in the above format it will be displayed

In the above case, if only columns like : id | name | email | gender is needed or otherwise date_of_birth is not needed then :

                                ||
                                \/
                                  
            SELECT id,name,email,gender FROM employee;

                                ||
                                \/

            SELECT name FROM employees;

John Doe Rose (3 Rows)

            SELECT date_of_birth FROM employees;

1990-01-01 1992-01-03 1995-03-03 (3 Rows)

            SELECT name,email FROM employees;

name | email -----+------------ John | john@gmail.com Doe | doe@gmail.com Rose | rose@gmail.com

            SELECT * FROM employees ORDER BY name ASC;

            SELECT * FROM employees ORDER BY name DESC;

existing table :

{see here I added one more field as entry 4 for Jack, question : Display all the date of births only but unique - because Jack and John has same date-of-birth}

            SELECT DISTINCT date_of_birth FROM employees;

Difference between \d and \dt =>

\d :

The \d command is a general-purpose command to describe database objects.
When used without any arguments, it lists all tables, views, sequences, and indexes in the current database.
When used with a table name (e.g., \d employees), it provides detailed information about the structure of that specific table, including columns, data types, indexes, constraints, and any other relevant details.

--==--==--==--==--==--==--==--==--==

\dt : The \dt command is specifically used to list tables. When used without any arguments, it lists all tables in the current schema. It provides a summary of tables, including their names, owners, and schemas, but does not provide detailed information about the structure of each table.

--==-==-=-=-=-=-=-=-==-=-=-=-=-=-=-=-=-=-=-=-=-=-=

To fetch only male /female :

        SELECT * FROM employee WHERE gender = 'female';

If need only the "Row of a Person" :

            SELECT * FROM employee WHERE name = "John";

            SELECT * FROM employees WHERE date_of_birth = '1993-05-06';

                SELECT * FROM employee WHERE name = 'Rose' AND gender='female';

                SELECT * FROM employee WHERE name='Elisa' OR gender='female';

    SELECT * FROM employee WHERE date_of_birth < '2001-04-05';

                      SELECT * FROM employee LIMIT 2;

                      SELECT first_name || ' ' || last_name,email FROM names;

This is about concatenation :

                  company=# SELECT * FROM names;

                      ||
                      \/

company=# SELECT first_name || ' ' || last_name,email FROM names; ?column? | email ------------------+-------------------- John Doe | john@gmail.com Rahul Renjan | rahul@gmail.com Vishnu G R | grvishnu@gmail.com Aparna Ravi | aparna@gmail.com Abhijith K Anand | abhijith@gmail.com (5 rows)

                      ||
                      \/

company=# SELECT first_name || ' ' || last_name AS full_name,email FROM names; full_name | email ------------------+-------------------- John Doe | john@gmail.com Rahul Renjan | rahul@gmail.com Vishnu G R | grvishnu@gmail.com Aparna Ravi | aparna@gmail.com Abhijith K Anand | abhijith@gmail.com (5 rows)

          SELECT now();

returns the current date and time of the pSQL server.

company=# select now(); now

2024-07-24 14:52:51.87828+05:30 (1 row)

            SELECT id,name AS full_name,email,dob,gender,place FROM employee;

This is column aliasing : here "name" was replaced with full_name

                    SELECT email,dob AS date_of_birth,gender,place,id,name AS full_name FROM employee;

this query has 2 concepts : 1-> in which order the output has to come is defined , see the order

2-> aliasing is done using "AS", name AS full_name will be replaced by full_name after query.

company=# SELECT email,dob AS date_of_birth,gender,place,id,name FROM employee; email | date_of_birth | gender | place | id | name -----------------+---------------+--------+-----------+----+------- john@gmail.com | 1990-04-04 | M | Kolkata | 1 | John doe@gmail.com | 1992-05-03 | M | Punjab | 2 | Doe divya@gmail.com | 1997-05-05 | F | Jharkhand | 3 | Divya priya@gmail.com | 1999-12-25 | F | Noida | 4 | Priya raj@gmail.com | 1993-11-12 | M | Rajastan | 5 | Raja rahul@gmail.com | 1992-05-03 | M | Assam | 6 | Rahul (6 rows)

Purpose of Aliasing : To make heading of the output of a query more meaningful;

                SELECT first_name || ' ' || last_name AS full_name FROM names ORDER BY last_name DESC;

the approach is : 1) concat first name and last name and alias : full_name , now this output is again sorted in descending order based on last name;

company=# SELECT first_name || ' ' || last_name AS full_name,email FROM names ORDER BY last_name DESC; full_name | email ------------------+-------------------- Rahul Renjan | rahul@gmail.com Aparna Ravi | aparna@gmail.com Abhijith K Anand | abhijith@gmail.com Vishnu G R | grvishnu@gmail.com John Doe | john@gmail.com (5 rows)

    SELECT first_name,LENGTH(first_name) len FROM names ORDER BY len DESC;

bulb : only one column is chosen : first_name and its lenght is found then sorted in descending at last using length property.

                        ||
                        \/

ORDER BY is evaluated after the SELECT , ths column alias "len" will be there so they can be soreted in ascending or descending order.

Provide feedback

Saved searches

Use saved searches to filter your results more quickly

done deal

company=# select now(); now

FilesExpand file tree

1_querying_data.md

Latest commit

History

1_querying_data.md

File metadata and controls

done deal

company=# select now(); now