CREATE TABLE team
(teamid varchar(10)primary key,
teamname varchar(20),
city varchar(20),
coach varchar(10)
);
select("Team table");
INSERT INTO team VALUES('team1','Royal challengers','bangalore','Dravid');
INSERT INTO team VALUES('team2','Knight Riders','kolkata','Ganguly');
INSERT INTO team VALUES('team3','Super kings','chennai','Dhoni');
SELECT * FROM team ;
CREATE TABLE player
(playerid varchar(10)primary key,
playername varchar(20),
phone number(10),
age number(3),
teamname varchar(20),
captain varchar(5),FOREIGN KEY (teamname) REFERENCES team(teamname));
select("Player table");
INSERT INTO player VALUES('player1','sandeep',123456789,22,'Royal challengers','yes');
INSERT INTO player VALUES('player2','sanjay',987654321,19,'Royal challengers','no');
INSERT INTO player VALUES('player3','sarath',998765432,18,'Knight Riders','no');
INSERT INTO player VALUES('player5','arun',787666665,23,'Knight Riders','yes');
INSERT INTO player VALUES('player4','kushal',1234567891,24,'Super kings','no');
SELECT * FROM player;
CREATE TABLE match(
matchid varchar(10)primary key,
manofmatch varchar(20),
playerid varchar(20),
teamid varchar(20),
teamwon number(10),
stadiumid varchar(10),FOREIGN KEY (stadiumid) REFERENCES stadium(stadiumid)
);
select("Match table");
INSERT INTO match VALUES('match1','sandeep','player1','team1',2,'st1');
INSERT INTO match VALUES('match2','sanjay','player2','team2',3,'st2');
INSERT INTO match VALUES('match3','arun','player3','team3',1,'st3');
INSERT INTO match VALUES('match4','sandeep','player1','team1',2,'st1');
INSERT INTO match VALUES('match5','kushal','player4','team1',3,'st1');
INSERT INTO match VALUES('match6','kushal','player4','team1',4,'st1');
SELECT * FROM match;
CREATE TABLE stadium(
stadiumid varchar(10)primary key,
stadiumname varchar(20),
city varchar(20),
areaname varchar(20),
pincode number(10));
select("Stadium table");
INSERT INTO stadium VALUES('st1','stadium1','bangalore','area1',560037);
INSERT INTO stadium VALUES('st2','stadium2','chennai','area2',560024);
INSERT INTO stadium VALUES('st3','stadium3','kochi','area3',560021);
SELECT * FROM stadium;
CREATE TABLE matchwon(
teamid varchar(10),
stadiumid varchar(10),
matchesplayed number(10),
won number(20),
loss number(10),FOREIGN KEY (stadiumid) REFERENCES stadium(stadiumid));
select("matchwon table");
INSERT INTO matchwon VALUES('team1','st1',4,2,2);
INSERT INTO matchwon VALUES('team2','st2',4,3,1);
INSERT INTO matchwon VALUES('team3','st3',2,1,1);
SELECT * FROM matchwon;
select("Q1. Display the youngest player (in terms of age) Name, Team name, age in which he belongs of the tournament");
-- 1 Display the youngest player (in terms of age) Name, Team name, age in which he belongs of the tournament.
/*playername,playerid,age from player group by playerid order by age asc FETCH FIRST 1 ROWS ONLY;*/
select playername,playerid,age from player group by playerid order by age asc limit 1;
-- 2 List the details of the stadium where the maximum number of matches were played.
select("Q2. List the details of the stadium where the maximum number of matches were played");
select m.stadiumid,count(m.matchid),s.stadiumname from match m,stadium s where
m.stadiumid=s.stadiumid group by m.stadiumid order by count(m.matchid) desc limit 1;
select("Q3. List the details of the player who is not a captain but got the man_of _match award at least in two matches.");
-- 3 List the details of the player who is not a captain but got the man_of _match award at least in two matches.
select playerid from player where playerid not in(select playerid from player where captain = 'yes')and
playerid in(select m.playerid from match m group by m.manofmatch having count(m.manofmatch)>1);
select("Q4. Display the Team details who won the maximum matches.");
--4 Display the Team details who won the maximum matches.
select m.teamwon,count(m.teamwon) from match m group by m.teamwon
order by count(m.teamwon) desc limit 1;
-- 5 Display the team name where all its won matches played in the same stadium
select("Q5. Display the team name where all its won matches played in the same stadium");
select m.teamwon, t.teamname, m.stadiumid, s.stadiumname from match m, stadium s, team t where m.teamid = t.teamid and m.stadiumid = s.stadiumid
group by m.teamwon having count(distinct m.stadiumid) = 1;
Write, Run & Share SQLite queries online using OneCompiler's SQLite online editor and compiler for free. It's one of the robust, feature-rich online editor and compiler for SQLite. Getting started with the OneCompiler's SQLite editor is really simple and pretty fast. The editor shows sample boilerplate code when you choose language as 'SQLite' and start writing queries to learn and test online without worrying about tedious process of installation.
SQLite is an in-process C library that implements small, fast, serverless, zero-configuration, transactional SQL database engine.
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');
DROP TABLE table_name;
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 [UNIQUE] INDEX index_name on table_name(column_name);
DROP INDEX index_name ON table_name;
CREATE VIEW View_name AS
Query;
SELECT * FROM View_name;
ALTER View View_name AS
Query;
DROP VIEW View_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;