Kerala Plus Two Computer Application 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 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;
SHOW command is used to list entire database in our system.
mysql>SHOW DATABASES;
Data Types
1. Char – It is used to store fixed number of characters. It is declared as char(size).
2. Varchar – It is used to store characters but it uses only enough memory.
3. Dec or Decimal – It is used to store numbers with decimal point. It is declared as Dec (size, scale). We can store a total of size number of digits.
4. 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.
5. Smallint – Used to store small integers.
6. Date – It is used to store date. The format is yyyy-mm-dd.
Eg: ‘1977-05-28’.
7. Time – It is used to store time. The format is
DDL commands (3 commands)
- Create table
- Avertable
- Drop table
DML commands (4 commands)
- Select
- Insert
- Delete
- Update
DCL (Data Control Language) commands
- Grant
- Revoke
Rules for naming tables and columns
- The name may contain alphabets(A-Z, a-z), digits(0-9), underscore(_) 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
- Auto_increment
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 3 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.