 
PRAGMA foreign_keys = ON;
PRAGMA ignore_check_constraints = ON;

/*----- Create Tables -----*/

CREATE TABLE MOVIE (
 TITLE VARCHAR(255) NOT NULL,
 YEAR INTEGER NOT NULL,
 LENGTH INTEGER,
 INCOLOR CHAR(1),
 STUDIONAME CHAR(50),
 PRODUCERC INTEGER
);

CREATE TABLE MOVIESTAR (
 NAME CHAR(30) NOT NULL,
 ADDRESS VARCHAR(255),
 GENDER CHAR(1),
 BIRTHDATE DATETIME
);

CREATE TABLE STARSIN (
    MOVIETITLE VARCHAR(255) NOT NULL,
    MOVIEYEAR INTEGER NOT NULL,
    STARNAME CHAR(30) NOT NULL
);

CREATE TABLE MOVIEEXEC (
    CERT INTEGER NOT NULL,
    NAME CHAR(30),
    ADDRESS VARCHAR(255),
    NETWORTH INTEGER
);

CREATE TABLE STUDIO (
    NAME CHAR(50) NOT NULL,
    ADDRESS VARCHAR(255),
    PRESC INTEGER
);

/*----- Create Constraints -----*/
ALTER TABLE MOVIE ADD CONSTRAINT PK_MOVIE PRIMARY KEY(TITLE,YEAR);

ALTER TABLE MOVIESTAR ADD CONSTRAINT PK_MOVIESTAR PRIMARY KEY(NAME);

ALTER TABLE MOVIEEXEC ADD CONSTRAINT PK_MOVIEEXEC PRIMARY KEY(CERT);

ALTER TABLE STUDIO ADD CONSTRAINT PK_STUDIO PRIMARY KEY(NAME);

ALTER TABLE STARSIN ADD CONSTRAINT PK_STARSIN PRIMARY KEY(MOVIETITLE,MOVIEYEAR,STARNAME);

ALTER TABLE MOVIE ADD CONSTRAINT FK_MOVIE_MOVIEEXEC FOREIGN KEY(PRODUCERC) REFERENCES MOVIEEXEC(CERT);

ALTER TABLE MOVIE ADD CONSTRAINT FK_MOVIE_STUDIO FOREIGN KEY(STUDIONAME) REFERENCES STUDIO(NAME);

ALTER TABLE STARSIN ADD CONSTRAINT FK_STARSIN_MOVIE FOREIGN KEY(MOVIETITLE, MOVIEYEAR) REFERENCES MOVIE(TITLE, YEAR);

ALTER TABLE STARSIN ADD CONSTRAINT FK_STARSIN_MOVIESTAR FOREIGN KEY(STARNAME) REFERENCES MOVIESTAR(NAME);

/*------- Insert Studio ------------*/
INSERT INTO STUDIO
  VALUES ('Disney','500 S. Buena Vista Street',1);

INSERT INTO STUDIO
  VALUES ('USA Entertainm.','', 2);

INSERT INTO STUDIO
  VALUES ('Fox','10201 Pico Boulevard', 3);

INSERT INTO STUDIO
  VALUES ('Paramount','5555 Melrose Ave', 4);

INSERT INTO STUDIO
  VALUES ('MGM','MGM Boulevard', 5);
  
/*------- Insert MovieExec ------------  */
INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT, NETWORTH)
  VALUES ('George Lucas', 'Oak Rd.', 555, 200000000);

INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT, NETWORTH)
  VALUES ('Ted Turner', 'Turner Av.', 333, 125000000);

INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT, NETWORTH)
  VALUES ('Stephen Spielberg', '123 ET road', 222, 100000000);

INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT, NETWORTH)
  VALUES ('Merv Griffin', 'Riot Rd.', 199, 112000000);

INSERT INTO MOVIEEXEC (NAME, ADDRESS, CERT, NETWORTH)
  VALUES ('Calvin Coolidge', 'Fast Lane', 123, 20000000);

/*------- Insert Movie ------------*/
INSERT INTO MOVIE
  VALUES ('Pretty Woman', 1990, 119, 'Y', 'Disney', 199);

INSERT INTO MOVIE
  VALUES ('The Man Who Wasn''t There', 2001, 116, 'N', 'USA Entertainm.',
    555);

INSERT INTO MOVIE
  VALUES ('Logan''s run', 1976, NULL, 'Y', 'Fox', 333);

INSERT INTO MOVIE
  VALUES ('Star Wars', 1977, 124, 'Y', 'Fox', 555);

INSERT INTO MOVIE
  VALUES ('Empire Strikes Back', 1980, 111, 'Y', 'Fox', 555);

INSERT INTO MOVIE
  VALUES ('Star Trek', 1979, 132, 'Y', 'Paramount', 222);

INSERT INTO MOVIE
  VALUES ('Star Trek: Nemesis', 2002, 116, 'Y', 'Paramount', 123);

INSERT INTO MOVIE
  VALUES ('Terms of Endearment', 1983, 132, 'Y', 'MGM', 123);

INSERT INTO MOVIE
  VALUES ('The Usual Suspects', 1995, 106, 'Y', 'MGM', 199);

INSERT INTO MOVIE
  VALUES ('Gone With the Wind', 1938, 238, 'Y', 'MGM', 123);

/*------- Insert MovieStar ------------*/

INSERT INTO MOVIESTAR
  VALUES ('Jane Fonda', 'Turner Av.', 'F', '1977-07-07');

INSERT INTO MOVIESTAR
  VALUES ('Alec Baldwin', 'Baldwin Av.', 'M', '1977-07-06');

INSERT INTO MOVIESTAR
  VALUES ('Kim Basinger', 'Baldwin Av.', 'F', '1979-07-05');

INSERT INTO MOVIESTAR
  VALUES ('Harrison Ford', 'Prefect Rd.', 'M', '1955-05-05');

INSERT INTO MOVIESTAR
  VALUES ('Debra Winger', 'A way', 'F', '1978-06-05');

INSERT INTO MOVIESTAR
  VALUES ('Jack Nicholson', 'X path', 'M', '1949-05-05');
  
INSERT INTO MOVIESTAR
  VALUES ('Sandra Bullock', 'X path', 'F', '1948-12-05');

/*------- Insert StarsIn ------------*/
INSERT INTO STARSIN
  VALUES ('Star Wars', 1977, 'Kim Basinger');

INSERT INTO STARSIN
  VALUES ('Star Wars', 1977, 'Alec Baldwin');

INSERT INTO STARSIN
  VALUES ('Star Wars', 1977, 'Harrison Ford');

INSERT INTO STARSIN
  VALUES ('Empire Strikes Back', 1980, 'Harrison Ford');

INSERT INTO STARSIN
  VALUES ('The Usual Suspects', 1995, 'Jack Nicholson');

INSERT INTO STARSIN
  VALUES ('Terms of Endearment', 1983, 'Jane Fonda');

INSERT INTO STARSIN
  VALUES ('Terms of Endearment', 1983, 'Jack Nicholson');

INSERT INTO STARSIN
  VALUES ('The Usual Suspects', 1995, 'Sandra Bullock');
  



CREATE TABLE laptop(
 code int NOT NULL, 
 model varchar(4) NOT NULL, 
 speed decimal(4, 0) NOT NULL,
 ram decimal(4, 0) NOT NULL, 
 hd decimal(3, 0) NOT NULL, 
 price float NOT NULL,
 screen int NOT NULL);
 
 CREATE TABLE pc(
  code int NOT NULL ,
  model varchar(4) NOT NULL ,
  speed decimal(4, 0) NOT NULL ,
  ram decimal(4, 0) NOT NULL ,
  hd decimal(3, 0) NOT NULL ,
  cd varchar(3) NOT NULL ,
  price float NOT NULL 
);

CREATE TABLE product(
  maker char(1) NOT NULL ,
  model varchar(4) NOT NULL ,
  type varchar(7) NOT NULL 
);

CREATE TABLE printer(
  code int NOT NULL ,
  model varchar(4) NOT NULL ,
  color char(1) NOT NULL ,
  type varchar(6) NOT NULL ,
  price float NOT NULL 
);

ALTER TABLE laptop ADD CONSTRAINT PK_laptop PRIMARY KEY(code); 

ALTER TABLE pc ADD CONSTRAINT PK_pc PRIMARY KEY(code);

ALTER TABLE product ADD	CONSTRAINT PK_product PRIMARY KEY (model);

ALTER TABLE printer ADD CONSTRAINT PK_printer PRIMARY KEY(code);

ALTER TABLE laptop ADD CONSTRAINT FK_laptop_product FOREIGN KEY(model) REFERENCES product(model);

ALTER TABLE pc ADD	CONSTRAINT FK_pc_product FOREIGN KEY(model) REFERENCES product(model);

ALTER TABLE printer ADD	CONSTRAINT FK_printer_product FOREIGN KEY(model) REFERENCES product(model);

/*----Product------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ */
insert into product values('B','1121','PC');
insert into product values('A','1232','PC');
insert into product values('A','1233','PC');
insert into product values('E','1260','PC');
insert into product values('A','1276','Printer');
insert into product values('D','1288','Printer');
insert into product values('A','1298','Laptop');
insert into product values('C','1321','Laptop');
insert into product values('A','1401','Printer');
insert into product values('A','1408','Printer');
insert into product values('D','1433','Printer');
insert into product values('E','1434','Printer');
insert into product values('B','1750','Laptop');
insert into product values('A','1752','Laptop');
insert into product values('E','2111','PC');
insert into product values('E','2112','PC');
/*----PC------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ */
insert into pc values(1,'1232',500,64,5,'12x',600);
insert into pc values(2,'1121',750,128,14,'40x',850);
insert into pc values(3,'1233',500,64,5,'12x',600);
insert into pc values(4,'1121',600,128,14,'40x',850);
insert into pc values(5,'1121',600,128,8,'40x',850);
insert into pc values(6,'1233',750,128,20,'50x',950);
insert into pc values(7,'1232',500,32,10,'12x',400);
insert into pc values(8,'1232',450,64,8,'24x',350);
insert into pc values(9,'1232',450,32,10,'24x',350);
insert into pc values(10,'1260',500,32,10,'12x',350);
insert into pc values(11,'1233',900,128,40,'40x',980);
/*----Laptop------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ */
insert into laptop values(1,'1298',350,32,4,700,11);
insert into laptop values(2,'1321',500,64,8,970,12);
insert into laptop values(3,'1750',750,128,12,1200,14);
insert into laptop values(4,'1298',600,64,10,1050,15);
insert into laptop values(5,'1752',750,128,10,1150,14);
insert into laptop values(6,'1298',450,64,10,950,12);
/*----Printer------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------ */
insert into printer values(1,'1276','n','Laser',400);
insert into printer values(2,'1433','y','Jet',270);
insert into printer values(3,'1434','y','Jet',290);
insert into printer values(4,'1401','n','Matrix',150);
insert into printer values(5,'1408','n','Matrix',270);
insert into printer values(6,'1288','n','Laser',400);



/*----- Tables -----*/
CREATE TABLE BATTLES (
 NAME VARCHAR(20) NOT NULL ,
 DATE DATETIME NOT NULL
);

CREATE TABLE CLASSES(
 CLASS VARCHAR(50) NOT NULL ,
 TYPE VARCHAR(2) NOT NULL ,
 COUNTRY VARCHAR (20) NOT NULL ,
 NUMGUNS INT, 
 BORE REAL, 
 DISPLACEMENT INT
);

CREATE TABLE SHIPS(
 NAME VARCHAR(50) NOT NULL ,
 CLASS VARCHAR(50) NOT NULL ,
 LAUNCHED INT
);

CREATE TABLE OUTCOMES(
 SHIP VARCHAR(50) NOT NULL ,
 BATTLE VARCHAR(20) NOT NULL ,
 RESULT VARCHAR(10) NOT NULL 
);

/*----- Constraints -----*/
ALTER TABLE BATTLES ADD	CONSTRAINT PK_BATTLES PRIMARY KEY(NAME);

ALTER TABLE CLASSES ADD	CONSTRAINT PK_CLASSES PRIMARY KEY(CLASS);

ALTER TABLE SHIPS ADD CONSTRAINT PK_SHIPS PRIMARY KEY(NAME);

ALTER TABLE SHIPS ADD CONSTRAINT FK_SHIPS_CLASSES FOREIGN KEY(CLASS) REFERENCES CLASSES(CLASS);

ALTER TABLE OUTCOMES ADD CONSTRAINT PK_OUTCOMES PRIMARY KEY(SHIP,BATTLE);

ALTER TABLE OUTCOMES ADD CONSTRAINT FK_OUTCOMES_BATTLES FOREIGN KEY(BATTLE) REFERENCES BATTLES(NAME);

ALTER TABLE OUTCOMES ADD CONSTRAINT FK_OUTCOMES_SHIPS FOREIGN KEY(SHIP) REFERENCES SHIPS(NAME);

/*----- Classes ----- */
INSERT INTO CLASSES
  VALUES ('Bismarck', 'bb', 'Germany', 8, 15, 42000);

INSERT INTO CLASSES
  VALUES ('Iowa', 'bb', 'USA', 9, 16, 46000);

INSERT INTO CLASSES
  VALUES ('Kongo', 'bc', 'Japan', 8, 14, 32000);

INSERT INTO CLASSES
  VALUES ('North Carolina', 'bb', 'USA', 12, 16, 37000);

INSERT INTO CLASSES
  VALUES ('Renown', 'bc', 'Gt.Britain', 6, 15, 32000);

INSERT INTO CLASSES
  VALUES ('Revenge', 'bb', 'Gt.Britain', 8, 15, 29000);

INSERT INTO CLASSES
  VALUES ('Tennessee', 'bb', 'USA', 12, 14, 32000);

INSERT INTO CLASSES
  VALUES ('Yamato', 'bb', 'Japan', 9, 18, 65000);

/*----- Battles ----- */
INSERT INTO BATTLES
  VALUES ('Guadalcanal', '1942-11-15');

INSERT INTO BATTLES
  VALUES ('North Atlantic', '1941-05-25');

INSERT INTO BATTLES
  VALUES ('North Cape', '1943-12-26');

INSERT INTO BATTLES
  VALUES ('Surigao Strait', '1944-10-25');

/*----- Ships ----- */
INSERT INTO SHIPS
  VALUES ('California', 'Tennessee', 1921);

INSERT INTO SHIPS
  VALUES ('Haruna', 'Kongo', 1916);

INSERT INTO SHIPS
  VALUES ('Hiei', 'Kongo', 1914);

INSERT INTO SHIPS
  VALUES ('Iowa', 'Iowa', 1943);

INSERT INTO SHIPS
  VALUES ('Kirishima', 'Kongo', 1915);

INSERT INTO SHIPS
  VALUES ('Kongo', 'Kongo', 1913);

INSERT INTO SHIPS
  VALUES ('Missouri', 'Iowa', 1944);

INSERT INTO SHIPS
  VALUES ('Musashi', 'Yamato', 1942);

INSERT INTO SHIPS
  VALUES ('New Jersey', 'Iowa', 1943);

INSERT INTO SHIPS
  VALUES ('North Carolina', 'North Carolina', 1941);

INSERT INTO SHIPS
  VALUES ('Ramillies', 'Revenge', 1917);

INSERT INTO SHIPS
  VALUES ('Renown', 'Renown', 1916);

INSERT INTO SHIPS
  VALUES ('Repulse', 'Renown', 1916);

INSERT INTO SHIPS
  VALUES ('Resolution', 'Renown', 1916);

INSERT INTO SHIPS
  VALUES ('Revenge', 'Revenge', 1916);

INSERT INTO SHIPS
  VALUES ('Royal Oak', 'Revenge', 1916);

INSERT INTO SHIPS
  VALUES ('Royal Sovereign', 'Revenge', 1916);

INSERT INTO SHIPS
  VALUES ('Tennessee', 'Tennessee', 1920);

INSERT INTO SHIPS
  VALUES ('Washington', 'North Carolina', 1941);

INSERT INTO SHIPS
  VALUES ('Wisconsin', 'Iowa', 1944);

INSERT INTO SHIPS
  VALUES ('Yamato', 'Yamato', 1941);
  
INSERT INTO SHIPS
  VALUES ('Yamashiro', 'Yamato', 1947);
  
INSERT INTO SHIPS
  VALUES ('South Dakota', 'North Carolina', 1941);

INSERT INTO SHIPS
  VALUES ('Bismarck', 'North Carolina', 1911);
  
INSERT INTO SHIPS
  VALUES ('Duke of York', 'Renown', 1916);
  
INSERT INTO SHIPS
  VALUES ('Fuso', 'Iowa', 1940);
  
INSERT INTO SHIPS
  VALUES ('Hood', 'Iowa', 1942);
  
INSERT INTO SHIPS
  VALUES ('Rodney', 'Yamato', 1915);
  
INSERT INTO SHIPS
  VALUES ('Yanashiro', 'Yamato', 1918);
  
INSERT INTO SHIPS
  VALUES ('Schamhorst', 'North Carolina', 1917);
  
INSERT INTO SHIPS
  VALUES ('Prince of Wales', 'North Carolina', 1937);
  
INSERT INTO SHIPS
  VALUES ('King George V', 'Iowa', 1942);
  
INSERT INTO SHIPS
  VALUES ('West Virginia', 'Iowa', 1942);

/*----- Outcomes ----- */
INSERT INTO OUTCOMES
  VALUES ('Bismarck', 'North Atlantic', 'sunk'); 

INSERT INTO OUTCOMES
  VALUES ('California', 'Surigao Strait', 'ok');

INSERT INTO OUTCOMES
  VALUES ('Duke of York', 'North Cape', 'ok');

INSERT INTO OUTCOMES
  VALUES ('Fuso', 'Surigao Strait', 'sunk');

INSERT INTO OUTCOMES
  VALUES ('Hood', 'North Atlantic', 'sunk');

INSERT INTO OUTCOMES
  VALUES ('King George V', 'North Atlantic', 'ok');

INSERT INTO OUTCOMES
  VALUES ('Kirishima', 'Guadalcanal', 'sunk');

INSERT INTO OUTCOMES
  VALUES ('Prince of Wales', 'North Atlantic', 'damaged');

INSERT INTO OUTCOMES
  VALUES ('Rodney', 'North Atlantic', 'ok');

INSERT INTO OUTCOMES
  VALUES ('Schamhorst', 'North Cape', 'sunk');

INSERT INTO OUTCOMES
  VALUES ('South Dakota', 'Guadalcanal', 'damaged');

INSERT INTO OUTCOMES
  VALUES ('Tennessee', 'Surigao Strait', 'ok');

INSERT INTO OUTCOMES
  VALUES ('Washington', 'Guadalcanal', 'ok');

INSERT INTO OUTCOMES
  VALUES ('West Virginia', 'Surigao Strait', 'ok');

INSERT INTO OUTCOMES
  VALUES ('Yamashiro', 'Surigao Strait', 'sunk');

INSERT INTO OUTCOMES
  VALUES ('California', 'Guadalcanal', 'damaged');














SELECT STUDIONAME
FROM Movie
WHERE inColor='Y';

created 2 years ago

SQLite online editor

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.

About SQLite

SQLite is an in-process C library that implements small, fast, serverless, zero-configuration, transactional SQL database engine.

Key Features:

Very small and light weight
Serverless
Free to use
Self contained as no other dependencies required.
zero config

Syntax help

Useful Commands

1. CREATE

CREATE TABLE table_name (
                column1 datatype,
                column2 datatype,
                ....);

Example

CREATE TABLE EMPLOYEE (
  empId INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  dept TEXT NOT NULL
);

2. ALTER

ALTER TABLE Table_name ADD column_name datatype;

Example

INSERT INTO EMPLOYEE VALUES (0001, 'Dave', 'Sales');

3. DROP

DROP TABLE table_name;

4. INSERT

INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);

Note: Column names are optional.

Example

INSERT INTO EMPLOYEE VALUES (0001, 'Ava', 'Sales');

5. SELECT

SELECT column1, column2, ...
FROM table_name
[where condition];

Example

SELECT * FROM EMPLOYEE where dept ='sales';

6. UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example

UPDATE EMPLOYEE SET dept = 'Sales' WHERE empId='0001';

7. DELETE

DELETE FROM table_name where condition;

Example

DELETE from EMPLOYEE where empId='0001';

8. CREATE INDEX

  CREATE [UNIQUE] INDEX index_name on table_name(column_name);

9. DROP INDEX

DROP INDEX index_name ON table_name;

10. Create a View

CREATE VIEW View_name AS 
Query;

11. How to call view

SELECT * FROM View_name;

12. Altering a View

ALTER View View_name AS 
Query;

13. Deleting a View

DROP VIEW View_name;

14. INNER JOIN

SELECT * FROM TABLE1 INNER JOIN TABLE2 where condition;

15. LEFT JOIN

SELECT * FROM TABLE1 LEFT JOIN TABLE2 ON condition;

16. RIGHT JOIN

SELECT * FROM TABLE1 RIGHT JOIN TABLE2 ON condition;

17. CROSS JOIN

SELECT select_list from TABLE1 CROSS JOIN TABLE2;