6/recent/ticker-posts

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


Post a Comment

0 Comments