Kerala Plus Two Computer Science Notes Chapter 9 Structured Query Language
SQL – Structured Query Language developed at IBM’s San Jose Research Lab.
The result of the compilation of DDL statements is a set of tables, which are stored in a special file called a data dictionary.
Creating a database in Mysql CREATE DATABASE <database_name>;
Eg:
mysql>CREATE DATABASE BVM;
Opening a database
USE command used to use a database
USE <database_name>;
Eg:
mysql>USE BVM;
The SHOW command is used to list the entire database in our system.
mysql>SHOW DATABASES;
Data Types
- Char – It is used to store a fixed number of characters. It is declared as char(size).
- Varchar – It is used to store characters but it uses only enough memory.
- Dec or Decimal – It is used to store numbers
with decimal points. It is declared as Dec (size, scale). We can store a total of size number of digits. . - Int or Integer- It is used to store numbers with¬out decimal point. It is declared as int. It has no argument. Eg: age int.
- Smallest – Used to store small integers.
- Date – It is used to store date. The format is yyyy- mm-dd. Eg: ‘1977-05-28’!
- Time – It is used to store time. The format is
DDL commands (3 commands)
- Create table
- Alter table
- Drop table
DML commands (4 commands)
- Select
- Insert
- Delete
- Update
DCL (Data Control Language) commands
1. Grant
2. Revoke
Rules for naming tables and columns
- The name may contain alphabets(A-Z, a-z),digits(0-9), underscore (J and dollar ($) symbol
- The name must contain at least one character.
- Special characters cannot be used except _ and $
- Cannot be a keyword
- The name must be unique.
Constraints are used to ensure database integrity.
- Not Null
- Unique
- Primary key
- Default
- Autojncrement
Order By:
Used to sort rows either in ascending (asc) or descending (desc) order.
Aggregate functions
- Sum()- find the total of a column.
- Avg()-find the average of a column.
- Min()- find the smallest value of a column.
- Max() – find the largest value of the column.
- Count()-find the number of values in a column.
Group by clause is used to group the rows. Having clause is used with Group By to give conditions.