Scribd Logo
Upload

Welcome to Scribd!

  • 8 views

    10SQL DDL

    Uploaded by

    yash150820044
    DBMS practical with code

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd

    10SQL DDL

    Uploaded by

    yash150820044
    8 views4 pages
    DBMS practical with code

    Copyright

    © © All Rights Reserved

    Available Formats

    TXT, PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    DBMS practical with code

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    8 views4 pages

    10SQL DDL

    Uploaded by

    yash150820044
    DBMS practical with code

    Copyright:

    © All Rights Reserved
    Download as TXT, PDF, TXT or read online from Scribd
    Download as txt, pdf, or txt
    You are on page 1of 4

    ASSIGNMENT 2

    SQL DDL COMMANDS


    ...............................................................
    mysql> create database info;
    Query OK, I row affected (001 sec)

    mysql> use info;


    Database changed

    Create Table:

    mysql> create table pereson_detail (Fname varchar(20), Mname varchar(20), Lname


    varchar(20), Address varchar(20),city varchar(20));
    Query OK, 0 rows affected (0.05 sec)

    Insert Value:

    mysql> insert into pereson_detail values('swati', 'vinod', 'gaikwad', 'mudhawa' ,


    'pune');
    Query OK, I row affected (0.01 sec)

    mysql> insert into pereson_detail values('renuka','anil', 'jadhav',


    'hadpser','lonavala);
    Query OK, I row affected (0.01 sec)

    mysql> insert into pereson_detail values('jiya', 'rahul', 'punekar', 'godpadi',


    'solapur');
    Query OK, I row affected (0.01 sec)

    mysql> insert into pereson_detail values('jiya','rahul, 'punekar', 'godpadi',


    'delhi');
    Query OK, I row affected (001 sec)

    mysql> insert into pereson_detail values('akshata','anil','kubhar', 'viman nager',


    'mumbai');
    Query OK, I row affected (001 sec)

    mysql> select*from pereson_detail;


    +------+-------+-------+------------+--------+
    Fname | Mname | Lname | Address | city |
    +------+-------+-------+------------+--------+
    swati | vinod |gaikwad| mudhawa |pune |
    renuka |anil | jadhav| hadpser |lonavala|
    jiya |rahul |punekar| godpadi |solapur |
    jiya |rahul |punekar| godpadi | delhi |
    akshata| anil |kubhar | viman nager| mumbai |
    +------+-------+-------+------------+--------+

    5 rows in set (0.00 sec)

    Modify Value:

    mysql> update pereson_detail set city='chennai' where Fname 'jiya';


    Query OK, 2 rows affected (0.01 sec)
    Rows matched: 2 Changed: 2 Warnings: 0

    mysql> select*from pereson_detail;


    +------+-------+-------+------------+--------+
    Fname | Mname | Lname | Address | city |
    +------+-------+-------+------------+--------+
    swati | vinod |gaikwad| mudhawa |pune |
    renuka |anil | jadhav| hadpser |lonavala|
    jiya |rahul |punekar| godpadi |chennai |
    jiya |rahul |punekar| godpadi |chennai |
    akshata| anil |kubhar | viman nager| mumbai |
    +------+-------+-------+------------+--------+
    5 rows in set (0.00 sec)

    Select Command:

    mysql> select fname, Address,city from pereson_detail;

    +------+------------+--------+
    Fname | Address | city |
    +------+------------+--------+
    swati | mudhawa |pune |
    renuka |hadpser |lonavala|
    jiya | godpadi |solapur |
    jiya | godpadi | delhi |
    akshata| viman nager| mumbai |
    +------+------------+--------+
    5 rows in set (0.00 sec)

    Delete Command:

    mysql> delete from pereson_detail where Fname='jiya';


    Query OK, 2 rows affected (0.01 sec)

    mysql> select*from pereson_detail;

    +------+-------+-------+------------+--------+
    Fname | Mname | Lname | Address | city |
    +------+-------+-------+------------+--------+
    swati | vinod |gaikwad| mudhawa |pune |
    renuka |anil | jadhav| hadpser |lonavala|
    akshata| anil |kubhar | viman nager| mumbai |
    +------+-------+-------+------------+--------+
    3 rows in set (0.00 sec)

    Where:

    mysql> select Address from pereson_detail where city='pune';


    +------------+
    | Address |
    +------------+
    | mudhawa |
    +------------+
    1 row in set (0.00 sec)

    Order By:

    1. DESC:

    mysql> select*from pereson_detail order by Lname desc;

    +------+-------+-------+------------+--------+
    Fname | Mname | Lname | Address | city |
    +------+-------+-------+------------+--------+
    akshata| anil |kubhar | viman nager| mumbai |
    renuka |anil | jadhav| hadpser |lonavala|
    swati | vinod |gaikwad| mudhawa |pune |
    +------+-------+-------+------------+--------+
    3 rows in set (0.01 sec).

    2. ASC

    mysql> select*from pereson_detail order by Lname asc;


    +------+-------+-------+------------+--------+
    Fname | Mname | Lname | Address | city |
    +------+-------+-------+------------+--------+
    swati | vinod |gaikwad| mudhawa |pune |
    renuka |anil | jadhav| hadpser |lonavala|
    akshata| anil |kubhar | viman nager| mumbai |
    +------+-------+-------+------------+--------+
    3 rows in set (0.00 sec)

    Group by:

    Mysql> select sum(Fname), city from pereson_detail group by city;

    TABLE

    3 rows in set, 3 warnings (0.00 sec)

    Having:

    mysql> select sum(Mname) from pereson_detail group by city having city


    in('pune','mumbai');

    TABLE

    2 rows in set, 3 warnings (0.00 sec)

    Alter:

    1. ADD

    mysql> alter table pereson_detail add AdherNo int;


    Query OK, 0 rows affected (0.03 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> select*from pereson_detail;

    +------+-------+-------+------------+--------+---------+
    Fname | Mname | Lname | Address | city | AdherNo |
    +------+-------+-------+------------+--------+---------+
    swati | vinod |gaikwad| mudhawa |pune | NULL |
    renuka |anil | jadhav| hadpser |lonavala| NULL |
    akshata| anil |kubhar | viman nager| mumbai | NULL |
    +------+-------+-------+------------+--------+---------+
    3 rows in set (0.00 sec)

    2. DROP:

    mysql> alter table pereson_detail drop AdherNo,


    Query OK, 0 rows affected (0.02 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> select * from pereson_detail;

    +------+-------+-------+------------+--------+
    Fname | Mname | Lname | Address | city |
    +------+-------+-------+------------+--------+
    swati | vinod |gaikwad| mudhawa |pune |
    renuka |anil | jadhav| hadpser |lonavala|
    akshata| anil |kubhar | viman nager| mumbai |
    +------+-------+-------+------------+--------+
    3 rows in set (0.00 sec)

    You might also like