Plus Two Computer Application Notes Chapter 9 Structured Query Language

by

Kerala State Board New Syllabus Plus Two Computer Application Notes Chapter 9 Structured Query Language.

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 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;

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.