-- create
CREATE TABLE Trainer_Info (
Trainer_Id varchar2(20) primary key,
Salutation varchar2(7),
Trainer_Name varchar2(30),
Trainer_Location varchar2(30),
Trainer_Track varchar2(15),
Trainer_Qualification varchar2(100),
Trainer_Experiance integer(11),
Trainer_Email varchar2 (100),
Trainer_Password varchar2 (20)
);
create table Batch_Info(
Batch_Id varchar2(20) primary key,
Batch_Owner varchar2(30),
Batch_BU_Name varchar2(30)
);
create table Module_Info(
Module_Id varchar2(20) primary key,
Module_Name varchar2(40),
Module_Duration Integer(11)
);
create table Associate_Info(
Associate_Id varchar2(20) primary key,
Salutation varchar2(7),
Associate_Name varchar2(30),
Associate_Location varchar2(30),
Associate_Track varchar2(15),
Associate_Qualification varchar2(200),
Associate_Email varchar2(100),
Associate_Password varchar2(20)
);
create table Questions(
Question_Id varchar2(20) primary key,
Module_Id varchar2(20),
Question_Text varchar2(900)
);
create table Associate_Status(
Associate_Id varchar2(20) ,
Module_Id varchar2(20),
Batch_Id varchar2(20),
Trainer_Id varchar2(20),
Start_Date varchar2(20),
End_Date varchar2(20)
);
create table Trainer_Feedback(
Trainer_Id varchar2(20) primary key,
Question_Id varchar2(20),
Batch_Id varchar2(20),
Module_Id varchar2(20),
Trainer_Rating Integer(11)
);
create table Associate_Feedback (
Associate_Id varchar2(20) primary key,
Question_Id varchar2(20),
Module_Id varchar2(20),
Associate_Rating Integer(11)
);
create table Login_Details(
User_Id varchar2(20) primary key,
User_Password varchar2(20)
);
insert into Trainer_Info (Trainer_Id,Salutation,Trainer_Name,Trainer_Location,Trainer_Track,Trainer_Qualification,Trainer_Experiance,Trainer_Email,Trainer_Password)
values
('F001','Mr.','PANKAJ GHOSH','Pune','Java','Bachelor of Technology',12,'[email protected]','fac1@123'),
('F002','Mr.','SANJAY RADHAKRISHNAN ','Bangalore','DotNet','Bachelor of Technology',12,'[email protected]','fac2@123'),
('F003','Mr.','VIJAY MATHUR','Chennai','Mainframe','Bachelor of Technology',10,'[email protected]','fac3@123'),
('F004','Mrs.','NANDINI NAIR','Kolkata','Java','Master of Computer Applications',9,'[email protected]','fac4@123'),
('F005','Miss.','ANITHA PAREKH','Hyderabad','Testing','Master of Computer Applications',6,'[email protected]','fac5@123'),
('F006','Mr.','MANOJ AGRAWAL' ,'Mumbai','Mainframe','Bachelor of Technology',9,'[email protected]','fac6@123'),
('F007','Ms.','MEENA KULKARNI','Coimbatore','Testing','Bachelor of Technology',5,'[email protected]','fac7@123'),
('F009','Mr.','SAGAR MENON' ,'Mumbai','Java','Master of Science In Information Technology',12,'[email protected]','fac8@123');
--select * from Trainer_Info;
insert into Batch_Info
(Batch_Id,Batch_Owner,Batch_BU_Name)
values
('B001','MRS.SWATI ROY','MSP'),
('B002','MRS.ARURNA K','HEALTHCARE'),
('B003','MR.RAJESH KRISHNAN','LIFE SCIENCES'),
('B004','MR.SACHIN SHETTY','BFS'),
('B005','MR.RAMESH PATEL','COMMUNICATIONS'),
('B006','MRS.SUSAN CHERIAN','RETAIL & HOSPITALITY'),
('B007','MRS.SAMPADA JAIN','MSP'),
('B008','MRS.KAVITA REGE','BPO'),
('B009','MR.RAVI SEJPAL','MSP');
--select * from Batch_Info;
insert into Module_Info
(Module_Id,Module_Name,Module_Duration)
values
('O10SQL','Oracle 10g SQL' ,16),
('O10PLSQL','Oracle 10g PL/ SQL' ,16),
('J2SE','Core Java SE 1.6',288),
('J2EE','Advanced Java EE 1.6',80),
('JAVAFX','JavaFX 2.1',80),
('DOTNT4','.Net Framework 4.0' ,50),
('SQL2008','MS SQl Server 2008',120),
('MSBI08','MS BI Studio 2008',158),
('SHRPNT','MS Share Point' ,80),
('ANDRD4','Android 4.0',200),
('EM001','Instructor',0),
('EM002','Course Material',0),
('EM003','Learning Effectiveness',0),
('EM004','Environment',0),
('EM005','Job Impact',0),
('TM001','Attendees',0),
('TM002','Course Material',0),
('TM003','Environment',0);
--select * from Module_Info;
insert into Associate_Info values
('A001','Miss.','GAYATHRI NARAYANAN','Gurgaon','Java','Bachelor of Technology','[email protected]','tne1@123'),
('A002','Mrs.','RADHIKA MOHAN','Kerala','Java','Bachelor of Engineering In Information Technology','[email protected]','tne2@123'),
('A003','Mr.','KISHORE SRINIVAS','Chennai','Java','Bachelor of Engineering In Computers','[email protected]','tne3@123'),
('A004','Mr.','ANAND RANGANATHAN','Mumbai','DotNet','Master of Computer Applications','[email protected]','tne4@123'),
('A005','Miss.','LEELA MENON','Kerala','Mainframe','Bachelor of Engineering In Information Technology','[email protected]','tne5@123'),
('A006','Mrs.','ARTI KRISHNAN','Pune','Testing','Master of Computer Applications','[email protected]','tne6@123'),
('A007','Mr.','PRABHAKAR SHUNMUGHAM','Mumbai','Java','Bachelor of Technology','[email protected]','tne7@123');
--select * from Associate_Info;
insert into Questions values
('Q001','EM001','Instructor knowledgeable and able to handle all your queries'),
('Q002','EM001','All the topics in a particular course handled by the trainer without any gaps or slippages'),
('Q003','EM002','The course materials presentation, handson, etc. refered during the training are relevant and useful'),
('Q004','EM002','The Hands on session adequate enough to grasp the understanding of the topic.'),
('Q005','EM002','The reference materials suggested for each module are adequate.'),
('Q006','EM003','Knowledge and skills presented in this training are applicatible at your work'),
('Q007','EM003','This training increases my proficiency level'),
('Q008','EM004','The physical environment e.g. classroom space, air-conditioning was conducive to learning.'),
('Q009','EM004','The software/hardware environment provided was sufficient for the purpose of the training.'),
('Q010','EM005','This training will improve your job performance.'),
('Q011','EM005','This training align with the business priorities and goals.'),
('Q012','TM001','Participants were receptive and had attitude towards learning.'),
('Q013','TM001','All participats gained the knowledge and the practical skills after this training.'),
('Q014','TM002','The course materials presentation, handson, etc. available for the session covers the entire objectives of the course.'),
('Q015','TM002','Complexity of the course is adequate for the particpate level.'),
('Q016','TM002','Case study and practical demos helpful in understanding of the topic'),
('Q017','TM003','The physical environment e.g. classroom space, air-conditioning was conducive to learning.'),
('Q018','TM003','The software/hardware environment provided was adequate for the purpose of the training.');
--select * from Questions;
insert into Associate_Status values
('A001','O10SQL','B001','F001','2000-12-15','2000-12-25'),
('A002','O10SQL','B001','F001','2000-12-15','2000-12-25'),
('A003','O10SQL','B001','F001','2000-12-15','2000-12-25'),
('A001','O10PLSQL','B002','F002','2001-2-1','2001-2-12'),
('A002','O10PLSQL','B002','F002','2001-2-1','2001-2-12'),
('A003','O10PLSQL','B002','F002','2001-2-1','2001-2-12'),
('A001','J2SE','B003','F003','2002-8-20','2002-10-25'),
('A002','J2SE','B003','F003','2002-8-20', '2002-10-25'),
('A001','J2EE','B004','F004','2005-12-1','2005-12-25'),
('A002','J2EE','B004','F004','2005-12-1','2005-12-25'),
('A003','J2EE','B004','F004','2005-12-1','2005-12-25'),
('A004','J2EE','B004','F004','2005-12-1','2005-12-25'),
('A005','JAVAFX','B005','F006','2005-12-4','2005-12-20'),
('A006','JAVAFX','B005','F006','2005-12-4','2005-12-20'),
('A006','SQL2008','B006','F007','2007-6-21','2007-6-28'),
('A007','SQL2008','B006','F007','2007-6-21','2007-6-28'),
('A002','MSBI08','B007','F006','2009-6-26','2009-6-29'),
('A003','MSBI08','B007','F006','2009-6-26','2009-6-29'),
('A004','MSBI08','B007','F006','2009-6-26','2009-6-29'),
('A002','ANDRD4','B008','F005','2010-6-5','2010-6-28'),
('A005','ANDRD4','B008','F005','2010-6-5','2010-6-28'),
('A003','ANDRD4','B009','F005','2011-8-1','2011-8-20'),
('A006','ANDRD4','B009','F005','2011-8-1','2011-8-20');
--select * from Associate_Status
/* excercise 3
update Trainer_Info
set Trainer_Password='nn4@123'
where Trainer_Id = 'F004';
select * from Trainer_Info;
4.
delete from Associate_Status
where Associate_Id='A003' and Module_Id='J2EE';
select * from Associate_Status
5.
SELECT * FROM Trainer_Info
order by Trainer_experiance desc
LIMIT 5;
6.
insert into login_details
values
('U001','Admin1@123'),
('U002' ,'Admin2@123')
;
rollback;
select * from login_details;
7.
drop login_details;
select * from login_details;
8.
create table suppliers(
supplier_id int(10) Not Null,
supplier_name varchar(50) Not Null,
address varchar(50)
);
9.
create table Course_Fees(
course_code varchar,
base_fees int,
special_fees int,
discount int,
unique (course_code, base_fees, special_fees)
);
insert into Course_Fees values
(1, 180, 100, 10),
(2, 150, 110, 10),
(3, 160, 170, 5),
(4, 150, 100, 10),
(6, 190, 100, 40);
create table Course_Fees_History(
COURSE_CODE varchar,
BASE_FEES int,
SPECIAL_FEES int,
CREATED_BY varchar,
Updated _By varchar
);
insert into Course_Fees_History values
(1, 120, 123, 'Ram','Ramesh'),
(2, 150, 110, 'Bala','Ram'),
(3, 160, 170, 'Bala','Vinu'),
(4, 170, 235, 'Ram' ,'Ram'),
(6, 190, 100, 'Vinod','Vinod');
10.
select course_code from Course_Fees
union
select course_code from Course_Fees_History;
11.
select course_code,base_fees,special_fees from Course_Fees
union
select course_code,base_fees,special_fees from Course_Fees_History;
12.
create table COURSE_INFO(
COURSE_CODE varchar(10) PRIMARY KEY,
COURSE_NAME varchar(20) NOT NULL,
COURSE_DESCRIPTION varchar(25),
COURSE_START_DATE Date,
COURSE_DURATION int,
NO_OF_PARTICIPANTS int,
COURSE_TYPE Char(3)
);
create table Student_Info(
STUDENT_ID varchar(10) PRIMARY KEY,
FIRST_NAME varchar(20),
LAST_NAME varchar(25),
ADDRESS varchar(150)
);
12.
select min(base_fees), max(base_fees) from course_fees;
13.
select avg(infra_fees) from course_fees;
14.
select course_name, datediff(sysdate,course_start_date) from course_info;
15.
select *, concat(course_name,' ',course_code) from course_info;
16.*/
create table Course_Fees(
course_code varchar primary key,
base_fees int not null,
special_fees int not null,
discount int
);
Write, Run & Share MySQL queries online using OneCompiler's MySQL online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for MySQL. Getting started with the OneCompiler's MySQL editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'MySQL' and start writing queries to learn and test online without worrying about tedious process of installation.
MySQL is a open-source, free and very popular relational database management system which is developed, distributed and supported by Oracle corporation.
CREATE TABLE table_name (
column1 datatype,
column2 datatype,
....);
CREATE TABLE EMPLOYEE (
empId INTEGER PRIMARY KEY,
name TEXT NOT NULL,
dept TEXT NOT NULL
);
ALTER TABLE Table_name ADD column_name datatype;
INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');
TRUNCATE table table_name;
DROP TABLE table_name;
RENAME TABLE table_name1 to new_table_name1;
--Line1;
/* Line1,
Line2 */
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
Note: Column names are optional.
INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');
SELECT column1, column2, ...
FROM table_name
[where condition];
SELECT * FROM EMPLOYEE where dept ='sales';
UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';
DELETE FROM table_name where condition;
DELETE from EMPLOYEE where empId='0001';
CREATE INDEX index_name on table_name(column_name);
CREATE UNIQUE INDEX index_name on table_name(column_name);
DROP INDEX index_name ON table_name;
Creating a View:
CREATE VIEW View_name AS
Query;
SELECT * FROM View_name;
ALTER View View_name AS
Query;
DROP VIEW View_name;
CREATE TRIGGER trigger_name trigger_time trigger_event
ON tbl_name FOR EACH ROW [trigger_order] trigger_body
/* where
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } */
DROP TRIGGER [IF EXISTS] trigger_name;
CREATE PROCEDURE sp_name(p1 datatype)
BEGIN
/*Stored procedure code*/
END;
CALL sp_name;
DROP PROCEDURE sp_name;
SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;
SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;
SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;
SELECT select_list from TABLE1 CROSS JOIN TABLE2;