MySQL : DDL and DML Statements

DDL Statements:

DDL statements or commands are used to define and modify the database structure of your tables or schema. When you execute a DDL statement, it takes effect immediately.

Some commands of DDL are:

• CREATE – to create table (objects) in the database
• ALTER – alters the structure of the database
• DROP – delete table from the database
• TRUNCATE – remove all records from a table, including all spaces allocated for the records are removed
• COMMENT – add comments to the data dictionary
• RENAME – rename a table

DML Statements

Data Manipulation Language (DML) statements or commands are used for managing data within tables. Some commands of DML are:

Some commands of DML are:

• SELECT – retrieve data from the a database
• INSERT – insert data into a table
• UPDATE – updates existing data within a table
• DELETE – deletes all records from a table, the space for the records remain
• MERGE – UPSERT operation (insert or update)
• CALL – call a PL/SQL or Java subprogram
• LOCK TABLE – control concurrency

Schema Diagram

Query with Solution

1.   Write a query to create a table 'electricity_connection_type'.

      Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:  

            create table electricity_connection_type

            (id INT(11) primary key,connection_name VARCHAR(20) not null);

2.    Write a query to create a table 'slab'.

       Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:

            create table slab(id INT(11) primary key,connection_type_id INT(11) not null,

            from_unit INt(11) not null,to_unit int(11) not null,rate Double not null , 

            FOREIGN KEY(connection_type_id) REFERENCES 

            electricity_connection_type(id));

3.    Write a query to create a table 'building_type'.

       Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:

            create table building_type(id int(11) primary key, name varchar(100) not null,

            connection_type_id int(11) not null, foreign key(connection_type_id) 

            references electricity_connection_type(id));

4.    Write a query to create a table 'building'.

       Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:

            create table building(id int(11) primary key,owner_name varchar(100) not null, 

            address varchar(100) not null, building_type_id int(11) not null,contact_number 

            varchar(100) not null,email_address varchar(100),foreign key(building_type_id)

            references building(connection_type_id));

5.    Write a query to alter the column 'owner_name' to 'building_owner_name' in the 

       table 'building'.

       Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:

            ALTER TABLE building CHANGE owner_name building_owner_name varchar(100);

6.    Write a query to change the datatype of the column address in the table 'building' to

       varchar(255).

       Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:

            ALTER TABLE building MODIFY address varchar(255);

7.    Write a query to create a constraint which allows only 'commercial' or 'home'

       connection_name in the 'electricity_connection_type' table.Specify constraint

       name as 'check_connection_name'.

       Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:

            ALTER TABLE electricity_connection_type ADD CONSTRAINT

            check_connection_name check('connection_name'='commercial' or 

            'connection_name'='home');

8.    Write a query to rename table 'building' to 'building_details'.

       Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:

            ALTER TABLE building RENAME TO building_details;

9.    Write a query to drop table 'slab'.

       Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:

            drop table slab;

10.    Write a query to drop table 'building_details'.

         Hint: Refer the schema diagram for the field names and its data type of the table.

Ans:

            drop table building_details;

11.    Write a query to insert any 2 records into the 'electricity_connection_type' table.

Ans:

            insert into electricity_connection_type values(1,'home');

            insert into electricity_connection_type values(2,'Comercial');

12.    Write a query to insert any 3 records into the 'slab' table.

Ans:

            insert into slab values(1,1,2,8,1.0);

            insert into slab values(2,2,10,15,2.0);

            insert into slab values(3,2,20,25,3.0);

13.    Write a query to insert any 5 records into the 'building_type' table.

Ans:

            insert into building_type values(1,'fsociety',1),(2,'MrRobot',2),(3,'Eliot',1),

            (4,'Tony',2),(5,'steve',1);

14.    Write a query to insert any 5 records into the 'building' table.

Ans:

            insert into building values

            (1,'MrRobot','ziety',1,7888987879,'ziety@gmail.com'),

            (2,'eliot','ziety',2,7888987868,'ziety@gmail.com'),

            (3,'erlene','ziety',1,7888987857,'ziety@gmail.com'),

            (4,'zang','ziety',2,7888987846,'ziety@gmail.com'),

            (5,'stark','ziety',1,7888987835,'ziety@gmail.com');

15.    Write a query to change the from_unit value from 0 to 1 in the 'slab' table.

Ans:

            update  slab set from_unit = 1 where from_unit = 0;

16.    Write a query to change the name 'Shopping Mall' to 'Mall' in the building_type table.

Ans:

            update building_type

            set name = 'Mall'

            where name = 'Shopping Mall';

17.    Write a query to change the owner_name 'Haidil' to 'Aiden' in the 'building' table.

Ans:

            update building

            set owner_name = 'Aiden'

            where owner_name = 'Haidil';

18.    Write a query to delete the entire details of the table 'slab'.

Ans:

            truncate table slab;

19.    Write a query to delete the rows from the table 'building_type' where

        the electricity connection is home .

        Hint: The connection_type_id for home is 1.

Ans:

            delete from  building_type

            where connection_type_id = 1;

20.    Write a query to delete the details of building whose owner_name is 

        'Muhammad Hafaz'.

Ans:

            delete from building 

            where owner_name = 'Muhammad Hafaz';