DBMS and SQL (OLD) - 12 Computer Sc 03 Databases Management Systems

 CBSE Class 12 Computer Science 3

Databases Management Systems and SQL
Revision Notes (Database Concepts and SQL)

---

Relations: In relational data model, the data is organised into tables(in the form of rows and columns) called relations.

Domain: A domain is the sets of values from which the actual values appearing in a given column are drawn.

Tuple: A row in a relation.

Attribute: A column in a relation.

Primary Key: A column or set of columns that uniquely identifies a row within a table is called primary key.

Candidate Key: Candidate keys are set of fields (columns with unique values) in the relation that are eligible to act as a primary key.

Alternate Key: A candidate key that is not the primary key is called alternate key.

DDL: Data Definition Language (DDL) or Data Description Language (DDL).

DML: Data Manipulation Language (DML).

String datatypes: CHAR, VARCHAR, VARCHAR2

Numeric datatype: NUMBER, NUMERIC, INT, FLOAT, DECIMAL

Date: DATE

Selection: Selection in relational algebra returns those tuples (records) in a relation that fulfil a condition (Produce table containing subset of rows).

Projection: Projection in relational algebra returns those columns in a relation that given in the attribute list (Produce table containing subset of columns).

Union: The union operator is used to combine two or more tables. In the union operation, duplicate records will be automatically removed from the resultant table.

Cartesian product: SQL joins are used to relate information in different tables. Cartesian product returns a number of rows equal to number of rows in the first table multiply by number of rows in the second table. At the same time, number of columns equal to number of columns in the first table added by number of columns in the second table.

SQL (Structured Query Language)

CREATE TABLE: Used to create the structure of a table.
CREATE TABLE table_name (column1 datatype, column2 datatype, column3  datatype,....);

ALTER TABLE: Used to implement structure modification.

To add a new column after creating table:
ALTER TABLE table_name ADD column_name datatype; 

To modify an existing column.
ALTER TABLE table_name MODIFY COLUMN column_name datatype;

DROP TABLE: To remove a table  and remove all of its data
DROP TABLE table_name ;

INSERT INTO: is used to insert new records in a table.

INSERT INTO table_name (column1,column2,column3, ..) VALUES(value1, value2, value3, ..);

SELECT: The SELECT statement is used to select and display data from a database.
SELECT column1, column2, ... FROM table_name;
SELECT * FROM table_name;
SELECT column1, column2, ... FROM table_name WHERE condition;

DISTINCT: Distinct keyword eliminates duplicate rows from the result of a select statement.
SELECT DISTINCT column1, column2, ... FROM table_name;

ORDER BY : Used to sort the result-set in ascending or descending order.
SELECT column1, column2, ... FROM table_name ORDER BY  column1 ... ASC|DESC;

GROUP BY : Used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns
SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s)

HAVING : Places condition on groups.

SELECT column_name(s) FROM table_name WHERE condition GROUP BY column_name(s) HAVING condition

MAX (): To select the maximum value of a particular column.

             SELECT MAX(column_name) FROM table_name WHERE condition;

MIN (): To select the minimum value of a particular column.

              SELECT MIN(column_name) FROM table_name WHERE condition;

SUM (): To find the total value of a particular column.

               SELECT SUM(column_name) FROM table_name WHERE condition;

AVG (): To find the average value of a particular column.

             SELECT AVG(column_name) FROM table_name WHERE condition;

COUNT (): Returns the number of records in the table.

             SELECT COUNT(column_name) FROM table_name WHERE condition;

UPDATE : Used to modify the existing records in a table.

             UPDATE table_name SET column1 = value1, column2 = value2, ...WHERE condition;

DELETE : Used to delete existing records in a table.

             DELETE FROM table_name WHERE condition;