Tamilnadu Samacheer Kalvi 12th Computer Science Notes Chapter 15 Data Manipulation Through SQL Notes
- A database is an organized collection of data.
- Users of database can be human users, other programs or applications
SQLite is a simple relational database system, which saves its data in regular data files.
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:
- GROUP BY
- ORDER BY.
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 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 is the master table which holds the key information about your database tables.
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’.