In the previous article, we learned about the basics of SQL and how it differs from NoSQL. Let's try some database queries now. You can read about Database Fundamentals and SQL Databases any time.

Demo of SQL Commands: 

First of all create a account on ocale live and sign-in. 
After login click on start coding, a work-sapce will open as below picture. 

We will write our command in "SQL Worksheet" section and after clicking "Run" button which is on top-right corner we can see output or error mesage in "SQL Statement Output" section. 

Now Lets write our first command. 

We will go through commands for Data Defination language(DDL) and Data Manupulation language (DML) which are subset of SQL.

CREATE command : It is used to create table or schema in a database.

• Write the commands in your SQL Worksheet as shown in image below.

• After writing command click Run. 
• We have successfully create a table named "students"  and "test" with its column name. 

Syntex: 

CREATE TABLE table_name ( 
column_name1 data_type constraint, 
column_name2 data_type constraint, 
column_name3 data_type constraint, 
);

• To confirm that the tables are created go to Menu —>  Schema. You can see two schema which we have just created. 

Drop Command: It is used to delete table from your database. 

• Write the commands in your SQL Worksheet as shown in image below.

• DROP TABLE test;

Note 📋
Only select the above command and click run. This will run only drop command.

    DROP TABLE table_name;

Insert Command : 

• Now we will insert some values in table using INSERT command and display all data in table using SELECT and FROM clause. 

Above command will insert values in table student. 

Syntex :

INSERT INTO table_name  (column1 , column2)  VALUES (value1, value2)

INSERT ALL  
       INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
       INTO table_name(column1, column2, column_n) VALUES (expr1, expr2, expr_n)  
       INTO table_name (column1, column2, column_n) VALUES (expr1, expr2, expr_n)
SELECT * FROM dual;

In SQL, the ALTER TABLE command is used to modify an existing table structure.This commands are use to perform operaions on columns only. Here are some common use cases for the ALTER TABLE command:

1. Adding a Column: You can add a new column to an existing table using the ADD clause.

ALTER TABLE students ADD(City VARCHAR2(100) ); 

2. Renaming a Column: You can rename an existing column in table using the RENAME COLUMN old name TO new_name clause.

ALTER TABLE students RENAME COLUMN Roll_Number TO Roll_N

3. Modifying a Column: You can change the data type or other properties of an existing column using the MODIFY clause.

ALTER TABLE students MODIFY(Name VARCHAR(200))

You can verify it in schema . Go to Menu —> Schema —> students  

4. Dropping a Column: You can remove an existing column from a table using the DROP Column_name clause.

ALTER TABLE test DROP (Age)

5. Renaming a Table: You can rename an existing table using the RENAME clause.

RENAME students TO employees

If you attempt to access an old table, you'll encounter an error message stating "table or view does not exist," as shown in the figure.

From now onwards you have to use new name of table.

Dual Table

The DUAL table is a special one-row, one-column table (usually named DUMMY) present by default in Oracle and some other SQL database systems. It's often used in SQL queries to perform calculations or to retrieve a single value when there's no actual table involved. 

For example:

SELECT SYSDATE FROM DUAL;

This query retrieves the current date and time (SYSDATE) from the DUAL table.

SELECT 1+2 FROM DUAL;

This query prints the sum of two numbers 1 & 2 from DUAL table.

SELECT * FROM DUAL;

This Query essentially selects all columns from the DUAL table, which is often used in situations where you need to perform a query without referencing any real table data.

Data Query Language:

    SELECT * FROM table_name;

    SELECT column1, column2 FROM table_name;

    SELECT column1, column2 FROM table_name WHERE condition;

    SELECT column1, column2 FROM table_name WHERE condition1 AND condition2;

    SELECT column_name, COUNT(column_name) FROM table_name GROUP BY column_name;

    SELECT column_name, COUNT(column_name) FROM table_name GROUP BY column_name HAVING condition;

    SELECT column1, column2 FROM table_name ORDER BY column_name ASC|DESC;

    FROM > WHERE  > GROUP > HAVING > ORDER BY > SELECT

SQL Joins

SQL JOIN is a powerful feature that allows you to retrieve data from multiple tables based on a related column between them. When searching relational databases, it is crucial since the data is spread over several tables and needs to be merged in order to yield meaningful results. SQL supports several types of joins, including INNER JOIN, OUTER JOIN, and EXCLUSIVE JOIN. Now let’s explore each kind.

INNER JOIN:

An inner join is a type of database join that combines rows from two tables based on a condition, typically where a column value in one table matches a column value in the other table. An inner join returns only the rows that have matching values in both tables. This type of join is often used to retrieve data that exists in both tables, filtering out unmatched rows.

SELECT * FROM table1 
INNER JOIN table2 ON table1.column = table2.column;

• Example: Retrieving orders along with customer information where there's a match between the customer IDs in the "orders" and "customers" tables.

OUTER JOIN:

An outer join is a type of database join that combines rows from two tables even if there is no matching row in one of the tables. There are three types of outer joins:

1. LEFT JOIN (or LEFT OUTER JOIN):

   • Returns all rows from the left table and the matched rows from the right table. If there's no match, NULL values are returned for columns from the right table.

   SELECT * FROM table1 
   LEFT JOIN table2 ON table1.column = table2.column;
   

   • Example: Retrieving all customers along with their orders, including customers who haven't placed any orders yet.

2. RIGHT JOIN (or RIGHT OUTER JOIN):

   • Returns all rows from the right table and the matched rows from the left table. If there's no match, NULL values are returned for columns from the left table.

   SELECT * FROM table1 
   RIGHT JOIN table2 ON table1.column = table2.column;
   

   • Example: Retrieving all orders along with customer information, including orders with no associated customers.

3. FULL JOIN (or FULL OUTER JOIN):

   • Returns all rows when there's a match in either left or right table. If there's no match, NULL values are returned for columns from the table without a match.

   SELECT * FROM table1 
   FULL JOIN table2 ON table1.column = table2.column;
   

   • Example: Retrieving all customers and their orders, including customers without orders and orders without associated customers.

EXCLUSIVE JOIN:

An "exclusive outer join," sometimes referred to as an exclusive join, is not the same as an INNER JOIN, LEFT JOIN, RIGHT JOIN, or FULL JOIN in SQL. Rather, it refers to a theoretical operation that comprises only the rows in the other table that do not have a corresponding row.

An exclusive join, if it existed, would return only the rows from the two tables that do not have a matching row in the other table. However, standard SQL does not directly support this type of join.

To achieve an exclusive join, you typically use a combination of LEFT JOIN and WHERE clauses to filter out rows that have a match in the other table.

SELECT <columns> FROM <left_table> 
LEFT JOIN <right_table> ON <join_condition>
WHERE <right_table>.

So, while there isn't a dedicated SQL operator for an exclusive join, you can achieve similar results using a combination of other SQL constructs, such as LEFT JOIN and WHERE clauses.

Wrapping Up