GST Billing Software

header image

SQL

Erachana Line

2.1 What is SQL?

• SQL stands for Structured Query Language.

• SQL lets you create, access, manipulate and delete databases.


What Can SQL do?

• SQL can execute queries against a database.

• SQL can retrieve data from a database.

• SQL can insert records in a database.

• SQL can update records in a database.

• SQL can delete records from a database.

• SQL can create new databases.

• SQL can create new tables in a database.

• SQL can create stored procedures in a database.

• SQL can create views in a database.

• SQL can set permissions on tables, procedures, and views.

2.2 What is Query?

A query is a request for data or information from a database table or combination of tables.

2.3 What is Constrains?

Constraints enforce limits to the data or type of data that can be inserted/updated/deleted from a table. The whole purpose of constraints is to maintain the data integrity during an update/delete/insert into a table.

2.4 Properties of Constraints

  • SQL constraints are used to specify rules for the data in a table.
  • Constraints are used to limit the type of data that can go into a table.
  • This ensures the accuracy and reliability of the data in the table.

• If there is any violation between the constraint and the data action, the action is aborted.

  • Constraints can be column level or table level.

• Column level constraints apply to a column, and table level constraints apply to the whole table.

2.5 Types of Constraints

2.5.1 NOT NULL

  • By default, a column can hold NULL values.
  • The NOT NULL constraint enforces a column to NOT accept NULL values.

• This enforces a field to always contain a value, which means that you cannot insert a new record, or update a record without adding a value to this field.

2.5.2 UNIQUE constraint

  • The UNIQUE constraint ensures that all values in a column are different.

• Both the UNIQUE and PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.

  • A PRIMARY KEY constraint automatically has a UNIQUE constraint.

• However, you can have many UNIQUE constraints per table, but only one PRIMARY KEY constraint per table.

2.5.3 Primary Key Constraints

• The PRIMARY KEY constraint uniquely identifies each record in a database table.

• Primary keys must contain UNIQUE values, and cannot contain NULL values.

• A table can have only one primary key, which may consist of single or multiple fields.

2.5.4 FOREIGN KEY

• A FOREIGN KEY is a key used to link two tables together.

• A FOREIGN KEY is a field (or collection of fields) in one table that refers to the PRIMARY KEY in another table.

• The table containing the foreign key is called the child table, and the table containing the candidate key is called the referenced or parent table.

Look at the following two tables:

"Persons" table:

PersonID

LastName

FirstName

Age

1

Hansen

Ola

30

2

Svendson

Tove

23

3

Pettersen

Kari

20

"Orders" table:

OrderID

OrderNumber

PersonID

1

77895

3

2

44678

3

3

22456

2

4

24562

1

Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.

The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.

The "PersonID" column in the "Orders" table is a FOREIGN KEY in the "Orders" table.

The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

2.5.5 CHECK Constraint

• The CHECK constraint is used to limit the value range that can be placed in a column.

• If you define a CHECK constraint on a single column it allows only certain values for this column.

• If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.

2.5.6 DEFAULT Constraint

• The DEFAULT constraint is used to provide a default value for a column.

• The default value will be added to all new records IF no other value is specified.

2.5.7 Auto increment

• Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

• Often this is the primary key field that we would like to be created automatically every time a new record is inserted.

2.5.8 Referential integrity

Referential integrity is a property of data stating references within it are valid. In the context of relational databases, it requires every value of one attribute (column) of a relation (table) to exist as a value of another attribute (column) in a different (or the same) relation (table).


2.6 SQL Clauses

WHERE Clause: This clause is used to define the condition, extract and display only those records which fulfill the given condition

Syntax: SELECT column_name(s)

FROM table_name

WHERE condition;

GROUP BY Clause: It is used with SELECT statement to group the result of the executed query using the value specified in it. It matches the value with the column name in tables and groups the result accordingly.

Syntax: SELECT column_name(s)

FROM table_name

GROUP BY column_name;

HAVING clause: This clause is used in association with GROUP BY clause. It is applied to each group of result or the entire result as single group and much similar as WHERE clause, the only difference is you cannot use it without GROUP BY clause

Syntax: SELECT column_name(s)

FROM table_name

GROUP BY column_name

HAVING condition;

ORDER BY clause: This clause is to define the order of the query output either in ascending (ASC) or in descending (DESC) order. Ascending (ASC) is the default one but descending (DESC) is set explicitly.

Syntax: SELECT column_name(s)

FROM table_name

WHERE condition

ORDER BY column_name ASC|DESC;

USING clause: USING clause comes in use while working with SQL Joins. It is used to check equality based on columns when tables are joined. It can be used instead ON clause in Joins.

Syntax: SELECT column_name(s)

FROM table_name

JOIN table_name

USING (column_name);