Tamilnadu Samacheer Kalvi 12th Computer Science Notes Chapter 15 Data Manipulation Through SQL Notes

Database:

  • A database is an organized collection of data.
  • Users of database can be human users, other programs or applications

SQLite:
SQLite is a simple relational database system, which saves its data in regular data files.

Cursor:
Cursor is a control structure used to traverse and fetch the records of the database. All the SQL commands will be executed using cursor object only.

SELECT Statement in SQL:

  •  “Select” is the most commonly used statement in SQL
  • The SELECT Statement in SQL is used to retrieve or fetch data from a table in a database

Clauses in SQL:

  • SQL provides various clauses that can be used in the SELECT statements. ,
  • These clauses can be called through python script.
  • Almost all clauses will work with SQLite.

The various clause are:

  • DISTINCT
  • WHERE
  • GROUP BY
  • ORDER BY.
  • HAVING

i) SQL DISTINCT Clause :

  • The distinct clause is helpful when there is need of avoiding the duplicate values present in any specific columns/ table.
  • When we use distinct keyword only the unique values are fetched.

ii) SQL WHERE Clause :

  • The WHERE clause is used to extract only those records that fulfill a specified condition.
  • Where clause cannot be used along with ‘Group by’ clause.
  • The WHERE clause can be combined with AND, OR, and NOT operators.

iii) SQL GROUP BY Clause :

  • The SELECT statement can be used along with GROUP BY clause.
  • The GROUP BY clause groups records into summary rows. ’
  • It returns one records for each group.
  • It is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

iv) SQL ORDER BY Clause :

  • The ORDER BY Clause can be used along with the SELECT statement to sort the data of specific fields in an ordered way.
  • It is used to sort the result-set in ascending or descending order.

v) SQL HAVING Clause:

  • Having clause is used to filter data based on the group functions.
  • Having clause is similar to WHERE condition but can be used only with group functions.
  • Group functions cannot be used in WHERE Clause but can be used in HAVING clause.

Role of ‘AND’ and ‘OR’ operators:
The ‘AND’ and ‘OR’ operators are used to filter records based on more than one condition

Aggregate functions:

  • Aggregate functions are used to do operations from the values of the column and a single value is returned.
  • COUNT() function returns the number of rows in a table.
  • AVG() function retrieves the average of a selected column of rows in a table.
  • SUM() function retrieves the sum of a selected column of rows in a table.
  • MAX() function returns the largest value of the selected column.
  • MIN() function returns the smallest value of the selected column

Sqlite_master:
sqlite_master is the master table which holds the key information about your database tables.

Path:
The path of a file can be either represented as ‘/’ or using ‘\\’ in Python. For example the path can be specified either as ‘c:/pyprg/sql.csv’, or c:\\pvprg\\sql.csv’.

Samacheer Kalvi 12th Computer Science Notes