GST Billing Software

header image

DML

Erachana Line

5.1 Select

The SELECT statement is used to select data from a database.

The data returned is stored in a result table, called the result-set.

SELECT Syntax

SELECT column1, column2,...
FROM table_name;

Here, column1, column2, ... are the field names of the table you want to select data from.

EX:

SELECT countryname,CountryId from COUNTRY;

5.1.1 Select All

If you want to select all the fields available in the table, use the following syntax:

SELECT * FROM table_name;

EX: SELECT * from Country;

5.1.2 Select by using Where condition

when we want to restrict the query results to a specified condition. The SQL WHERE clause Can be used.

Syntax:

SELECT * FROM tableName WHERE condition;

EX: Select * from Country where Countryname=" india";

5.1.3 Select by using Left Join

The LEFT JOIN keyword returns all records from the left table (table1), and the matched records from the right table (table2). The result is NULL from the right side, if there is no match.

LEFT JOIN Syntax

SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.


SQL LEFT JOIN

EX:

SELECT STATE_NAME,PLACE_NAME FROM PLACE LEFT JOIN STATE ON PLACE.STATE_ID=STATE.STATE_ID;

5.1.4 Select by using Right Join

The RIGHT JOIN keyword returns all records from the right table (table2), and the matched records from the left table (table1). The result is NULL from the left side, when there is no match.

RIGHT JOIN Syntax

SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;

Note: In some databases RIGHT JOIN is called RIGHT OUTER JOIN.

SQL LEFT JOIN

EX:

SELECT STATE_NAME,PLACE_NAME FROM PLACE RIGHT JOIN STATE ON PLACE.STATE_ID=STATE.STATE_ID;

5.1.5 Select by using Outer Join

The FULL OUTER JOIN keyword returns all records when there is a match in either left (table1) or right (table2) table records.

Note: FULL OUTER JOIN can potentially return very large result-sets!

Syntax

SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name;

SQL LEFT JOIN

EX:

SELECT STATE_NAME,PLACE_NAME FROM PLACE FULL OUTER JOIN STATE ON PLACE.STATE_ID=STATE.STATE_ID;

5.1.6 Select by using Inner Join

The INNER JOIN keyword selects records that have matching values in both tables.

Syntax

SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;

SQL LEFT JOIN

EX:

SELECT STATE_NAME,PLACE_NAME FROM PLACE INNER JOIN STATE ON PLACE.STATE_ID=STATE.STATE_ID;

5.1.7 Select by using Aggregate function

An aggregate function allows you to perform a calculation on a set of values to return a single scalar value. We often use aggregate functions with the GROUP BY and HAVING clauses of the SELECT statement.

The following are the most commonly used SQL aggregate functions:

  • AVG - calculates the average of a set of values.
  • COUNT - counts rows in a specified table or view.
  • MIN - gets the minimum value in a set of values.
  • MAX - gets the maximum value in a set of values.
  • SUM - calculates the sum of values.

Notice that all aggregate functions above ignore NULL values except for the COUNT function.

COUNT function example

To get the number of the products in the products table, you use the COUNT function as follows:

EX: Select Count (*) from Customer;

AVG function example

To calculate the average units in stock of the products, you use the AVG function as follows:

EX: Select Avg (Age) from Customer;

SUM function example

To calculate the sum of units in stock by product category, you use the SUM function with the GROUP BY clause as the following query:

EX: Select Sum (Age) from Customer

Group by Customer.Place_id;

MIN function example

To get the minimum units in stock of products in the products table, you use the MIN function as follows:

EX: Select Min (Age) from Customer;

MAX function example

To get the maximum units in stock of products in the products table, you use the MAX function as shown in the following query:

EX: Select Max (Age) from Customer;


5.2 Insert

5.2.1 Insert with column

The SQL INSERT INTO Statement is used to add new rows of data to a table in the database.

Syntax

INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)

VALUES (value1, value2, value3, ... valueN);

Here, column1, column2, column3, ...columnN are the names of the columns in the table into which you want to insert the data.

Ex: INSERT INTO COUNTRY (COUNTRY_ID,COUNTRY_NAME) VALUES(3,'CHINA');

5.2.2 Insert without Column

You may not need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.

The SQL INSERT INTO syntax will be as follows −

INSERT INTO TABLE_NAME VALUES (value1,value2,value3,...valueN);

Ex: INSERT INTO DESIGNATION VALUES(1,'CSE');

5.2.3 Populate one table using another table

You can populate the data into a table through the select statement over another table; provided the other table has a set of fields, which are required to populate the first table.

Here is the syntax −

INSERT INTO first_table_name [(column1, column2, ... columnN)]

SELECT column1, column2, ...columnN

FROM second_table_name

[WHERE condition];


5.3 update

5.3.1 Update all

The SQL UPDATE Query is used to modify the existing records in a table.

Syntax:

UPDATE table_name SET column1=Value1;

Ex: UPDATE Customer SET Place=" bangalore";

5.3.2 Update using where condition

You can use the WHERE clause with the UPDATE query to update the selected rows, otherwise all the rows would be affected.

Syntax

The basic syntax of the UPDATE query with a WHERE clause is as follows −

UPDATE table_name

SET column1 = value1, column2 = value2...., columnN = valueN

WHERE [condition];

Ex: UPDATE Customer SET Place=" bangalore" where Customer_id=3;


5.4 delete

5.4.1 Delete All

The SQL DELETE Query is used to delete the existing records from a table.

DELETE FROM CUSTOMERS;

Now, the CUSTOMERS table would not have any record.

5.4.2 Delete by using where Condition

You can use the WHERE clause with a DELETE query to delete the selected rows, otherwise all the records would be deleted.

Syntax

DELETE FROM table_name

WHERE [condition];

EX:

DELETE FROM CUSTOMERS where Place="puttur";


5.5 Assignment

You already created Contact_Manager database now add atleast 10 rows for each table by using all DML syntax.