Plus Two Computer Science Notes Chapter 9 Structured Query Language

by

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

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.