Scribd Logo
Upload

Welcome to Scribd!

  • 35 views

    Program 2 and 3

    Uploaded by

    SATHYABAMA MADHANKUMAR
    The document describes SQL commands performed on databases and tables in MySQL. It includes: 1. Creating databases, tables, and inserting records into tables. Various tables like Employee, Department, Project etc. were created with different fields and sample records were inserted. 2. Altering tables by adding, modifying, renaming and dropping columns. The Project table was altered multiple times to demonstrate these. 3. Other commands shown include truncating tables to remove all records, renaming tables, and committing or rolling back transactions.

    Copyright:

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

    Program 2 and 3

    Uploaded by

    SATHYABAMA MADHANKUMAR
    35 views16 pages
    The document describes SQL commands performed on databases and tables in MySQL. It includes: 1. Creating databases, tables, and inserting records into tables. Various tables like Employee, Department, Project etc. were created with different fields and sample records were inserted. 2. Altering tables by adding, modifying, renaming and dropping columns. The Project table was altered multiple times to demonstrate these. 3. Other commands shown include truncating tables to remove all records, renaming tables, and committing or rolling back transactions.

    Copyright

    © © All Rights Reserved

    Available Formats

    PDF, TXT or read online from Scribd

    Did you find this document useful?

    Is this content inappropriate?

    The document describes SQL commands performed on databases and tables in MySQL. It includes: 1. Creating databases, tables, and inserting records into tables. Various tables like Employee, Department, Project etc. were created with different fields and sample records were inserted. 2. Altering tables by adding, modifying, renaming and dropping columns. The Project table was altered multiple times to demonstrate these. 3. Other commands shown include truncating tables to remove all records, renaming tables, and committing or rolling back transactions.

    Copyright:

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

    Program 2 and 3

    Uploaded by

    SATHYABAMA MADHANKUMAR
    The document describes SQL commands performed on databases and tables in MySQL. It includes: 1. Creating databases, tables, and inserting records into tables. Various tables like Employee, Department, Project etc. were created with different fields and sample records were inserted. 2. Altering tables by adding, modifying, renaming and dropping columns. The Project table was altered multiple times to demonstrate these. 3. Other commands shown include truncating tables to remove all records, renaming tables, and committing or rolling back transactions.

    Copyright:

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

    Program 2:

    Perform the following:


    a. Viewing all databases, Creating a Database, Viewing all Tables in a
    Database, Creating Tables (With and Without Constraints),
    Inserting/Updating/Deleting Records in a Table, Saving (Commit) and
    Undoing (rollback)
    mysql> show databases;
    +--------------------+
    | Database |
    +--------------------+
    | information_schema |
    | mysql |
    | performance_schema |
    | sakila |
    | sys |
    | world |
    +--------------------+
    6 rows in set (0.00 sec)
    DATABASE CREATION
    mysql> create database COMPANY1173;
    Query OK, 1 row affected (0.00 sec)

    ACTIVATE/USING DATABASE
    mysql> use COMPANY1173;
    Database changed
    DISPLAYING ALL THE TABLES:
    mysql> show tables;
    Empty set (0.00 sec)
    TABLE 1: EMPLOYEE1173
    TABLE CREATION
    mysql> create table EMPLOYEE1173(fname varchar(10), minit varchar(3),
    lname varchar(10), ssn integer(5), bdate date, address varchar(10), sex
    varchar(1), salary integer(8), superssn integer(5), dno varchar(5));
    Query OK, 0 rows affected, 3 warnings (0.02 sec)
    mysql> desc employee1173;

    INSERTION
    mysql> insert into employee1173 values('MURALI',' ','RAJ',222,'1992-04-03',
    'BANGALORE', 'M', 700000, 222,'D1');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into employee1173 values('NEHAN',' ','RAJ',333,'1995-06-02',


    'KERALA', 'M', 700000, 333,'D5');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into employee1173 values('SHILPA','S','KAUR',444,'1989-06-


    20', 'WESTBENGAL', 'F', 750000, 444,'D5');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into employee1173 values('SHAILA','S',' ',555,'1990-07-25',


    'KERALA', 'F', 446000, 555,'D5');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into employee1173 values('BANU',' ',' ',666,'1985-12-31',


    'ANDRA', 'F', 473000, 666, 'D5');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into employee1173 values('CHARAN','K K',' ',777,'1999-11-


    30', 'BANGALORE', 'F', 585500, 777,'D5');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into employee1173 values('DHARUN','M K',' ',888,'1997-02-


    05', 'BANGALORE', 'F', 535700, 888,'D5');
    Query OK, 1 row affected (0.00 sec)
    mysql> insert into employee1173 values('GOWRAV','A K','RAJU ','999','1987-
    06-05', 'BANGALORE', 'F', '735000','999','D2');
    Query OK, 1 row affected (0.00 sec)
    mysql> insert into employee1173 values('RAMU','g','RAGAV','101','2001-05-
    17', 'MYSORE', 'M', '885000', '101','D2');
    Query OK, 1 row affected (0.00 sec)
    mysql> insert into employee1173 values('RENU','K',' ','102','1987-07-15',
    'MYSORE', 'F', '570000', '102','D3');
    Query OK, 1 row affected (0.00 sec)
    mysql> insert into employee1173 values('JAYA','K K','SRI','103','1988-08-15',
    'TUMKUR', 'F', '880000', '103', 'D4');
    Query OK, 1 row affected (0.00 sec)

    DISPLAY OF ALL THE RECORDS


    mysql> SELECT * FROM EMPLOYEE1173;
    TABLE 2: DEPARTMENT1173
    TABLE CREATION
    mysql> create table DEPARTMENT1173(dname varchar(10), dno varchar(5),
    mgrssn integer(5), msrstartdate date);
    Query OK, 0 rows affected, 2 warnings (0.01 sec)
    mysql> desc department1173;

    INSERTION
    mysql> insert into department1173 values('Research','D1','1001','2000-01-04');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into department1173 values('Accounts','D2','1021','2000-01-04');


    Query OK, 1 row affected (0.00 sec)

    mysql> insert into department1173 values('HR','D3','1002','2000-01-04');


    Query OK, 1 row affected (0.00 sec)

    mysql> insert into department1173 values('Devlopment','D4','1003','2000-01-


    04');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into department1173 values('Testing','D5','1004','2000-01-04');


    Query OK, 1 row affected (0.00 sec)

    DISPLAY OF ALL THE RECORDS


    mysql> select * from department1173;

    TABLE 3: DEPT_LOC1173
    TABLE CREATION
    mysql> create table DEPT_LOC1173(dname varchar(10),dno varchar(5),
    dlocation varchar(15));
    Query OK, 0 rows affected (0.01 sec)
    mysql> desc dept_loc1173;
    INSERTION
    mysql> insert into dept_loc1173 values('D1','BANGALORE');
    Query OK, 1 row affected (0.00 sec)

    mysql> insert into dept_loc1173 values('D2','BANGALORE');


    Query OK, 1 row affected (0.00 sec)

    mysql> insert into dept_loc1173 values('D3','BANGALORE');


    Query OK, 1 row affected (0.00 sec)

    mysql> insert into dept_loc1173 values('D4','BANGALORE');


    Query OK, 1 row affected (0.00 sec)

    mysql> insert into dept_loc1173 values('D5','BANGALORE');


    Query OK, 1 row affected (0.00 sec)
    DISPLAY OF ALL THE RECORDS:
    mysql>select * from dept_loc1173;

    TABLE 4: PROJECT1173
    TABLE CREATION
    mysql> create table PROJECT1173(pname varchar(10),pno
    varchar(5),plocation varchar(10), dno varchar(5));
    Query OK, 0 rows affected (0.01 sec)
    mysql> desc PROJECT1173;

    INSERTION
    mysql> INSERT INTO PROJECT1173 VALUES ('WEBSITE', 'P1',
    'BANGALORE', 'D4');
    Query OK, 1 row affected (0.04 sec)

    mysql> INSERT INTO PROJECT1173 VALUES ('BILLING','P2','


    BANGALORE','D4');
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO PROJECT1173 VALUES ('ATTENDANCE','P2',


    'BANGALORE','D5');
    Query OK, 1 row affected (0.04 sec)
    DISPLAY OF ALL THE RECORDS:
    mysql> SELECT * FROM PROJECT1173;
    TABLE 5: WORKS_ON1173
    TABLE CREATION
    mysql> create table WORKS_ON1173(essn varchar(10), pno varchar(5), hours
    integer(3));
    Query OK, 0 rows affected, 1 warning (0.01 sec)
    mysql> DESC WORKS_ON1173;

    INSERTION
    mysql> INSERT INTO WORKS_ON1173 VALUES (333,'P2',160);
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO WORKS_ON1173 VALUES (444,'P2',160);


    Query OK, 1 row affected (0.04 sec)

    mysql> INSERT INTO WORKS_ON1173 VALUES (555,'P2',160);


    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO WORKS_ON1173 VALUES (666,'P2',160);


    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO WORKS_ON1173 VALUES(777,'P2',160);


    Query OK, 1 row affected (0.00 sec)
    mysql> INSERT INTO WORKS_ON1173 VALUES(888,'P2',160);
    Query OK, 1 row affected (0.00 sec)

    mysql> INSERT INTO WORKS_ON1173 VALUES(103,'P1',210);


    Query OK, 1 row affected (0.00 sec)
    DISPLAY OF ALL THE RECORDS
    mysql> SELECT * FROM WORKS_ON1173;

    TABLE 6: DEPENDENT1173
    TABLE CREATION
    mysql> create table DEPENDENT1173(essn varchar(10),dependent_name
    varchar(20), sex varchar(1),bdate date, relationship varchar(10));
    Query OK, 0 rows affected (0.02 sec)
    mysql> DESC DEPENDENT1173;
    INSERTION
    mysql> INSERT INTO DEPENDENT1173 VALUES('333','RAJA','M','2017-
    08-05','SON');
    Query OK, 1 row affected (0.00 sec)
    mysql> INSERT INTO DEPENDENT1173 VALUES ('555', 'ASHWINI', 'F',
    '2000-07-25', 'DAUGHTER');
    Query OK, 1 row affected (0.00 sec)
    DISPLAY OF ALL THE RECORDS:
    mysql> SELECT * FROM DEPENDENT1173;

    mysql> SHOW TABLES;

    UPDATION:
    mysql> SELECT * FROM EMPLOYEE1173;
    mysql> update employee1173 set sex='M' where ssn=777;
    Query OK, 1 row affected (0.00 sec)
    Rows matched: 1 Changed: 1 Warnings: 0

    mysql> select * from employee1173;

    DELETION OF RECORD
    mysql> SELECT * FROM DEPENDENT1173;
    mysql> delete from dependent1173 where essn=555;
    Query OK, 1 row affected (0.00 sec)

    mysql> select * from dependent1173;

    ROLLBACK
    mysql> rollback;
    Query OK, 0 rows affected (0.00 sec)

    COMMIT
    mysql> commit;
    Query OK, 0 rows affected (0.00 sec)

    PROGRAM – 3
    Perform the following:
    a. Altering a Table, Dropping/Truncating/Renaming Tables, Backing up /
    Restoring a Database.
    mysql> show databases;

    mysql> use company1173;


    Database changed
    mysql> show tables;

    mysql> DESC PROJECT1173;

    ALTER – ADD COLUMN


    mysql> alter table project1173 ADD COLUMN dname varchar(10);
    Query OK, 0 rows affected (0.02 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC PROJECT1173;

    ALTER – MODIFY COLUMN


    mysql> alter table project1173 MODIFY COLUMN dname varchar(20);
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0
    mysql> DESC PROJECT1173;

    ALTER – RENAME COLUMN


    mysql> alter table project1173 RENAME COLUMN dname TO dept_name;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> DESC PROJECT1173;


    mysql> SELECT * FROM PROJECT1173;

    ALTER – DROP COLUMN


    mysql> alter table project1173 DROP COLUMN DEPT_NAME;
    Query OK, 0 rows affected (0.01 sec)
    Records: 0 Duplicates: 0 Warnings: 0

    mysql> DESC PROJECT1173;

    mysql> COMMIT;
    Query OK, 0 rows affected (0.00 sec)
    ALTER – RENAME(TABLE NAME)

    mysql> alter table project1173 RENAME project_1173;


    Query OK, 0 rows affected (0.01 sec)
    mysql> show tables;

    TRUNCATE
    mysql> truncate table works_on1173;
    Query OK, 0 rows affected (0.04 sec)
    mysql> show tables;

    mysql> select * from works_on1173;


    Empty set (0.00 sec)

    You might also like