Database Concept
DBMS ER Model
DBMS Relational model
- DBMS Codd’s 12 rule
- DBMSBasic Concepts
- DBMS Relational Algebra
- DBMS Relational Calculus
- DBMS ER Model to Relational Model
- DBMS Types of Database key
DBMS Normalization
- DBMS Introduction to Normalization
- DBMS First Normal Form (1NF)
- DBMS Second Normal Form (1NF)
- DBMS Third Normal Form (3NF)
- DBMS Boyce-Codd Normal Form (BCNF)
- DBMS Fourth Normal Form (4NF)
- DBMS Fifth Normal Form (5NF)
DBMS Basic SQL
DBMS DML Command
DBMS DCL Command
- All DCL Command
- SELECT query
- WHERE clause
- LIKE clause
- ORDER BY clause
- Group BY clause
- Having clause
- DISTINCT keyword
- AND & OR operator
- DIVISION operator
Advanced SQL
SELECT
query is used to retrieve data from a table. It is the most used SQL query. We can retrieve complete table data, or partial by specifying conditions using the WHERE
clause.
Syntax of SELECT
query
SELECT
query is used to retieve records from a table. We can specify the names of the columns which we want in the resultset.
SELECT
column_name1,
column_name2,
column_name3,
...
column_nameN
FROM table_name;
Time for an Example
Consider the following student table,
s_id | name | age | address |
---|---|---|---|
101 | Adam | 15 | Chennai |
102 | Alex | 18 | Delhi |
103 | Abhi | 17 | Banglore |
104 | Ankit | 22 | Mumbai |
SELECT s_id, name, age FROM student;
The above query will fetch information of s_id
, name
and age
columns of the student table and display them,
s_id | name | age |
---|---|---|
101 | Adam | 15 |
102 | Alex | 18 |
103 | Abhi | 17 |
104 | Ankit | 22 |
As you can see the data from address
column is absent, because we did not specif it in our SELECT
query.
Select all records from a table
A special character asterisk *
is used to address all the data(belonging to all columns) in a query. SELECT
statement uses *
character to retrieve all records from a table, for all the columns.
SELECT * FROM student;
The above query will show all the records of student table, that means it will show complete dataset of the table.
s_id | name | age | address |
---|---|---|---|
101 | Adam | 15 | Chennai |
102 | Alex | 18 | Delhi |
103 | Abhi | 17 | Banglore |
104 | Ankit | 22 | Mumbai |
Select a particular record based on a condition
We can use the WHERE
clause to set a condition,
SELECT * FROM student WHERE name = 'Abhi';
The above query will return the following result,
103 | Abhi | 17 | Rohtak |
Performing Simple Calculations using SELECT
Query
Consider the following employee table.
eid | name | age | salary |
---|---|---|---|
101 | Adam | 26 | 5000 |
102 | Ricky | 42 | 8000 |
103 | Abhi | 25 | 10000 |
104 | Rohan | 22 | 5000 |
Here is our SELECT
query,
SELECT eid, name, salary+3000 FROM employee;
The above command will display a new column in the result, with 3000 added into existing salaries of the employees.
eid | name | salary+3000 |
---|---|---|
101 | Adam | 8000 |
102 | Ricky | 11000 |
103 | Abhi | 13000 |
104 | Rohan | 8000 |
So you can also perform simple mathematical operations on the data too using the SELECT
query to fetch data from table.