Scribd
Upload
6 views

MySql

Mysql

Uploaded by

jaijaigs05
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
6 views

MySql

Mysql

Uploaded by

jaijaigs05
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 5

SQL 1: E-BILL

1. TO CREATE TABLE
MySql> CREATE TABLE ebill (rrno varchar(10), Name char(25), billdate
date, units int(4));

2. TO INSERT RECORDS
MySql> INSERT INTO ebill VALUES ("A101","Ajay", "2024/02/05",55);

Mysql> INSERT INTO ebill VALUES ("A102", "Sujay", "2024/10/02",150);

Mysql> INSERT INTO ebill VALUES ("A103", "Vijay", "2024/08/04",300);

Mysql> INSERT INTO ebill VALUES ("A104","Rajesh","2024/07/06",75);

Mysql> INSERT INTO ebill VALUES ("A105","Ramesh","2024/12 /03",10);

3. MySql> ALTER TABLE ebill ADD (billamt int(10), duedate date);

4. MySql> DESC ebill;

5.
MySql> UPDATE ebill SET billamt = 50+ units*4.50 WHERE units <= 100;

6.
MySql> UPDATE ebill SET billamt = 50 + 100*4.50 + (units-100)*5.50
WHERE units >100;

6. MySql> UPDATE ebill SET duedate = billdate + 15;

7. MySql> SELECT * From ebill;

Output for 4th & 7th queries.


1. MySql> CREATE TABLE student (regno int(4), name char(20), M1
int(3), M2 int(3), M3 int(3), M4 int(3));

2. MySql> DESC student;

3.
MySql> INSERT INTO student VALUES(111, "Sumanth", 40, 60, 50, 70);
MySql> INSERT INTO student VALUES(102, "Vasanth", 20, 26, 40, 50);
MySql> INSERT INTO student VALUES(109, "Chris", 50, 60, 70, 80);
MySql> INSERT INTO student VALUES(100, "Tony", 23, 36, 25, 53);
MySql> INSERT INTO student VALUES (107, "Chandra", 45, 66, 55, 72);

4. MySql> SELECT * FROM student;

5.
MySql> ALTER TABLE student ADD (total int(4), percent int(4), result
char(4));
MySql> DESC student;
(Describing table after altering. This output should be written)

6.
MySql> UPDATE student SET total = M1+M2+M3+M4;
MySql> UPDATE student SET percent = total/4.0;

7.
MySql> UPDATE student SET result = "PASS" WHERE M1>=35 AND M2>=35 AND
M3>=35 AND M4>=35;
MySql> UPDATE student SET result = "FAIL" WHERE M1<35 OR M2<35 OR M3
<35 OR M4<35;

8. MySql> SELECT * FROM student WHERE result="PASS";

9. MySql> SELECT COUNT(*) FROM student WHERE result="FAIL";


10. MySql> SELECT * FROM student ORDER BY regno;

Outputs for the following queries:

5. MySql> desc student;

7.
MySql> UPDATE student SET result = "PASS" WHERE M1>=35 AND M2>=35 AND
M3>=35 AND M4>=35;

9. MySql> SELECT COUNT(*) FROM student WHERE result="FAIL";

10. MySql> SELECT * FROM student ORDER BY regno;


1. MySql> CREATE TABLE employee(empid int(4), deptid int(2) references
department(deptid), name varchar(20), salary int(5));

2. MySql> CREATE TABLE department(deptid int(2) PRIMARY KEY, deptname


varchar(20), supervisor varchar(20));

Write outputs for:


MySql> desc employee;
MySql> desc department;

3. Inserting 10 records into employee table


MySql> INSERT INTO employee VALUES(1011,01,"Arun", 25000);
MySql> INSERT INTO employee VALUES(1035,03,"Vijay", 30000);
MySql> INSERT INTO employee VALUES(1042,01, "Vinay", 50000);
MySql> INSERT INTO employee VALUES(1036,02, "Suman",35000);
MySql> INSERT INTO employee VALUES(1045,03,"Sujay", 40000);
MySql> INSERT INTO employee VALUES(1078,01, "Prem",43000);
MySql> INSERT INTO employee VALUES(1027,04, "Kiran",20000);
MySql> INSERT INTO employee VALUES(1017,02,"Amar", 15000);
MySql> INSERT INTO employee VALUES(1010,03,"Arun", 25000);
MySql> INSERT INTO employee VALUES(1071,04,"Arun",25000);

Inserting 4 records into department table


MySql> INSERT INTO department VALUES(01,"purchase", "Akbar");
MySql> INSERT INTO department VALUES(02,"accounts","Anthony");
MySql> INSERT INTO department VALUES(03,"sales", "Raghavendra");
MySql> INSERT INTO department VALUES(04,"production", "Harsha");
4. MySql> SELECT * FROM employee WHERE deptid = (SELECT deptid FROM
department WHERE deptname="accounts");

5. MySql> SELECT COUNT(*) FROM employee WHERE deptid = (SELECT deptid


FROM department WHERE deptname="sales");
6. MySql> SELECT MIN(salary), MAX(salary), AVG(salary) FROM employee
WHERE deptid = (SELECT deptid FROM department WHERE deptname="sales");

7. MySql> SELECT * FROM employee WHERE deptid = (SELECT deptid FROM


department WHERE supervisor ="Anthony");

8. MySql> UPDATE employee SET salary = salary + salary *0.15 WHERE


deptid = (SELECT deptid FROM department where deptname="sales");

9.MySql> ALTER TABLE employee ADD(bonus int(5));


MySql> UPDATE employee SET bonus = salary * 0.05;

Select * from employee; (note the output)

10.
MySql> DELETE FROM employee WHERE deptid = (SELECT deptid FROM
department WHERE deptname="production");

Outputs for the following queries:

2. MySql> desc employee;


MySql> desc department;

9. Select * from employee;

You might also like