IP

• SQL provides commands for defining the relation schemas, modifying relation schemas and deleting relations.
• These commands are known as Data Definition Language (DDL) through which the set of relations are specified, including their schema, data type for each attribute, the constraints as well as the security and access related authorisations.
• Data definition starts with the create statement, which is used to create a database and its tables (relations).
• Before creating a database, it is important to be clear about the number of tables in the database, the columns (attributes) in each table along with the data type of each column.
• This information decides the relation schema.
• To create a database, we use the CREATE DATABASE

  statement as shown in the following syntax:

  CREATE DATABASE da t a ba s e na me ;
• Write

  the following SQL statement for using the database:

  my s l > USE Student Attendance ;

  Database changed
• using the Show t a bl e s command that lists

  names of all the tables within a database.

  mys ql > SHOW TABLES;
• After creating database St ude nt At t e nda nc e , we need

  to define relations (create tables) in this database and

  specify attributes for each relation along with data types

  for each attribute. This is done using the CREATE TABLE

  statement.

  Syntax:

  CREATE TABLE t a bl e na me (

  a t t r i but e na me 1 da t a t ype c ons t r a i nt ,

  a t t r i but e na me 2 da t a t ype c ons t r a i nt ,

  :

  a t t r i but e na me N da t a t ype c ons t r a i nt ) ;
• We can view the structure of an already created table

  using the describe statement.

  Syntax:

  DESCRI BE t a bl e na me ;
• After creating a table we may realize that we need to

  add/remove an attribute or to modify the datatype of an

  existing attribute or to add constraint in attribute. In all

  such cases, we need to change or alter the structure of

  the table by using the alter statement.

  Syntax:

  ALTER TABLE (table name ADD/ Modify/ DROP attribute 1,

  attribute2, . .
• We can use DROP statement to

  remove a database or a table permanently from the

  system. However, one should be very cautious while

  using this statement as it cannot be undone.

  Syntax to drop a table:

  DROP TABLE t a bl e _na me ;
• Data Manipulation using a database means either

  retrieval (access) of existing data, insertion of new data,

  removal of existing data or modification of existing data

  in the database.
• to add a new column INSERT INTO statement is used to insert new records in a table. Its syntax is:

  INSERT INTO table name

  VALUES( value 1, value 2, . . . . ) ;
• The SQL statement SELECT is used to retrieve data from

  the tables in a database and the output is also displayed

  in tabular form.

  Syntax:

  SELECT attribute 1, attribute 2, . . .

  FROM (table name)

  WHERE condition
• DISTINCT Clause

  By default, SQL shows all the data retrieved through

  query as output. However, there can be duplicate values.

  The SELECT statement when combined with DISTINCT

  clause, returns records without repetition (distinct

  records). For example, while retrieving employee’s

  department number, there can be duplicate values as

  many employees are assigned to same department. To

  display unique department number for all the employees,

  we use DI STI NCT as shown below:

  my sql > SELECT DISTINCT Dept Id> FROM EMPLOYEE;
• The WHERE clause is used to retrieve data that meet

  some specified conditions. In the OFFICE database,

  more than one employee can have the same salary. To

  display distinct salaries of the employees working in the

  department number D01, we write the following query

  in which the condition to select the employee whose

  department number is D01 is specified using the WHERE

  clause:

  my sql > SELECT DISTINCT Salary

  > FROM EMPLOYEE

  • > WHERE Dept id=' D01' ;
• ORDER BY clause is used to display data in an ordered

  (arranged) form with respect to a specified column. By

  default, ORDER BY displays records in ascending order of

  the specified column’s values. To display the records in

  descending order, the DESC (means descending) keyword

  needs to be written with that column.
• Data Updation

  We may need to make changes in the value(s) of one or

  more columns of existing records in a table. For example,

  we may require some changes in address, phone number

  or spelling of name, etc. The UPDATE statement is used to

  make such modifications in the existing data.

  Syntax:

  UPDATE tabl e _name

  SET attribute 1 = value 1, where (column name)= value 2, . . .
• The DELETE statement is used to delete one or more

  record(s) from a table.

  Syntax:

  DELETE FROM table _name

  WHERE condition;
• A database is a collection of related tables.
• MySQL is a 'relational' DBMS.
• A table in a database is a collection of rows and columns, where each row is a record and columns describe the features of records.
• SQL is the standard language for most RDBMS and is case insensitive.
• The CREATE DATABASE statement is used to create a new database.
• The USE statement is used for making the specified database as active database.
• The CREATE TABLE statement is used to create a table.
• Every attribute in a CREATE TABLE statement must have a name and a datatype.
• The ALTER TABLE statement is used to make changes in the structure of a table like adding, removing or changing datatype of column(s).
• The DESC statement with table name shows the structure of the table.
• The INSERT INTO statement is used to insert record(s) in a table.
• The UPDATE statement is used to modify existing data in a table.
• The DELETE statement is used to delete records in a table.
• The LI KE operator makes use of the following two

  wild card characters:

  • % (percent)— used to represent zero, one, or multiple

  characters

  • _ (underscore)— used to represent a single character