Posted inphp / Mysql / Mysqli

sample sql database for practice

Today, We want to share with you sample sql database for practice.In this post we will show you sample employee table sql with data, hear for sample database download we will give you demo and example for implement.In this post, we will learn about how to run sql query in command prompt? with an example.

sql sample tables with data

In this tutorial we learn to all about sample sql database for practice Examples like as a sample database download, sample database for sql server 2014, sql server 2017 or many more.

SQL Server Sample Database

Table deals.shops

CREATE TABLE deals.shops (
	shop_id INT IDENTITY (1, 1) PRIMARY KEY,
	shop_name VARCHAR (255) NOT NULL,
	contact VARCHAR (25),
	email VARCHAR (255),
	street VARCHAR (255),
	city VARCHAR (255),
	state VARCHAR (10),
	zip_code VARCHAR (5)
);

Table deals.workers

CREATE TABLE deals.workers (
	worker_id INT IDENTITY (1, 1) PRIMARY KEY,
	profilenm VARCHAR (50) NOT NULL,
	sirnm VARCHAR (50) NOT NULL,
	email VARCHAR (255) NOT NULL UNIQUE,
	contact VARCHAR (25),
	active tinyint NOT NULL,
	shop_id INT NOT NULL,
	manager_id INT,
	FOREIGN KEY (shop_id) 
        REFERENCES deals.shops (shop_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (manager_id) 
        REFERENCES deals.workers (worker_id) 
        ON DELETE NO ACTION ON UPDATE NO ACTION
);

Table manufacturer.catalogueies

CREATE TABLE manufacturer.catalogueies (
	catalogue_id INT IDENTITY (1, 1) PRIMARY KEY,
	catalogue_name VARCHAR (255) NOT NULL
);

Table manufacturer.vendors

CREATE TABLE manufacturer.vendors (
	vendor_id INT IDENTITY (1, 1) PRIMARY KEY,
	vendor_name VARCHAR (255) NOT NULL
);

Table manufacturer.items

CREATE TABLE manufacturer.items (
	item_id INT IDENTITY (1, 1) PRIMARY KEY,
	item_name VARCHAR (255) NOT NULL,
	vendor_id INT NOT NULL,
	catalogue_id INT NOT NULL,
	model_year SMALLINT NOT NULL,
	list_price DECIMAL (10, 2) NOT NULL,
	FOREIGN KEY (catalogue_id) 
        REFERENCES manufacturer.catalogueies (catalogue_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (vendor_id) 
        REFERENCES deals.vendors (vendor_id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

Table deals.members

CREATE TABLE deals.members (
	member_id INT IDENTITY (1, 1) PRIMARY KEY,
	profilenm VARCHAR (255) NOT NULL,
	sirnm VARCHAR (255) NOT NULL,
	contact VARCHAR (25),
	email VARCHAR (255) NOT NULL,
	street VARCHAR (255),
	city VARCHAR (50),
	state VARCHAR (25),
	zip_code VARCHAR (5)
);

Table deals.requests

CREATE TABLE deals.requests (
	request_id INT IDENTITY (1, 1) PRIMARY KEY,
	member_id INT,
	request_status tinyint NOT NULL,
	-- Order status: 1 = Pending; 2 = Processing; 3 = Rejected; 4 = Completed
	request_date DATE NOT NULL,
	required_date DATE NOT NULL,
	shipped_date DATE,
	shop_id INT NOT NULL,
	worker_id INT NOT NULL,
	FOREIGN KEY (member_id) 
        REFERENCES deals.members (member_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (shop_id) 
        REFERENCES deals.shops (shop_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (worker_id) 
        REFERENCES deals.workers (worker_id) 
        ON DELETE NO ACTION ON UPDATE NO ACTION
);

Table deals.request_items

CREATE TABLE deals.request_items(
	request_id INT,
	item_id INT,
	item_id INT NOT NULL,
	quantity INT NOT NULL,
	list_price DECIMAL (10, 2) NOT NULL,
	discount DECIMAL (4, 2) NOT NULL DEFAULT 0,
	PRIMARY KEY (request_id, item_id),
	FOREIGN KEY (request_id) 
        REFERENCES deals.requests (request_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (item_id) 
        REFERENCES manufacturer.items (item_id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

Table manufacturer.stocks

CREATE TABLE manufacturer.stocks (
	shop_id INT,
	item_id INT,
	quantity INT,
	PRIMARY KEY (shop_id, item_id),
	FOREIGN KEY (shop_id) 
        REFERENCES deals.shops (shop_id) 
        ON DELETE CASCADE ON UPDATE CASCADE,
	FOREIGN KEY (item_id) 
        REFERENCES manufacturer.items (item_id) 
        ON DELETE CASCADE ON UPDATE CASCADE
);

SQL Table : sample sql database for practice

create the table members

Table MEMBERS

CREATE TABLE  "MEMBERS" 
   (	
    "MEMBER_CODE" CHAR(6) NOT NULL PRIMARY KEY, 
	"MEMBER_NAME" CHAR(40), 
	"WORKING_AREA" CHAR(35), 
	"COMMISSION" NUMBER(10,2), 
	"PHONE_NO" CHAR(15), 
	"COUNTRY" VARCHAR2(25) 
	 );

Now insert records into the table members :

INSERT INTO MEMBERS VALUES ('MD07', 'Ramasundar', 'Surat', '0.22', '077-25814763', '');
INSERT INTO MEMBERS VALUES ('MD03', 'Alex ', 'Rajkot', '0.13', '075-12458969', '');
INSERT INTO MEMBERS VALUES ('MD08', 'Alford', 'Junagdha', '0.12', '044-25874365', '');
INSERT INTO MEMBERS VALUES ('MD11', 'Ravi Kumar', 'Surat', '0.22', '077-45625874', '');
INSERT INTO MEMBERS VALUES ('MD10', 'Santakumar', 'Bhavanagar', '0.14', '007-22388644', '');
INSERT INTO MEMBERS VALUES ('MD12', 'Lucida', 'San Jose', '0.12', '044-52981425', '');
INSERT INTO MEMBERS VALUES ('MD05', 'Ajaydevgan', 'Morbi', '0.13', '045-21447739', '');
INSERT INTO MEMBERS VALUES ('MD01', 'Subbarao', 'Surat', '0.14', '077-98746674', '');
INSERT INTO MEMBERS VALUES ('MD02', 'Mukesh', 'Kalavad', '0.11', '029-98758964', '');
INSERT INTO MEMBERS VALUES ('MD06', 'McDen', 'Rajkot', '0.22', '078-22255588', '');
INSERT INTO MEMBERS VALUES ('MD04', 'Ivan', 'Jammnagar', '0.22', '008-22544166', '');
INSERT INTO MEMBERS VALUES ('MD09', 'Benjamin', 'Hampshair', '0.11', '008-22536178', '');

Table member

create the table member :

CREATE TABLE  "Member" 
   (	"MEMB_CODE" VARCHAR2(6) NOT NULL PRIMARY KEY, 
	"MEMB_NAME" VARCHAR2(40) NOT NULL, 
	"MEMB_CITY" CHAR(35), 
	"WORKING_AREA" VARCHAR2(35) NOT NULL, 
	"MEMB_COUNTRY" VARCHAR2(20) NOT NULL, 
	"GRADE" NUMBER, 
	"OPENING_AMT" NUMBER(12,2) NOT NULL, 
	"RECEIVE_AMT" NUMBER(12,2) NOT NULL, 
	"PAYMENT_AMT" NUMBER(12,2) NOT NULL, 
	"OUTSTANDING_AMT" NUMBER(12,2) NOT NULL, 
	"PHONE_NO" VARCHAR2(17) NOT NULL, 
	"MEMBER_CODE" CHAR(6) NOT NULL REFERENCES MEMBERS
);   

INSERT INTO Member VALUES ('JD9813', 'Bhakti', 'Rajkot', 'Rajkot', 'UK', '2', '1294.00', '4987.00', '1593.00', '4098.00', 'KKKKKKB', 'MD03');
INSERT INTO Member VALUES ('JD9801', 'Shital', 'Junagdha', 'Junagdha', 'Gondal', '2', '2854.00', '4987.00', '2098.00', '1294.00', 'CCCCCCC', 'MD08');
INSERT INTO Member VALUES ('JD9820', 'Rekha', 'Junagdha', 'Junagdha', 'Gondal', '3', '4987.00', '1593.00', '1294.00', '1294.00', 'KKKKSKK', 'MD08');
INSERT INTO Member VALUES ('JD9825', 'Krishna', 'Surat', 'Surat', 'Ahemdabad', '2', '4987.00', '1593.00', '4098.00', '5489.00', 'AVAVAVA', 'MD11');
INSERT INTO Member VALUES ('JD9824', 'Lella', 'Rajkot', 'Rajkot', 'UK', '2', '4098.00', '8989.00', '1593.00', '1294.00', 'FSDDSDF', 'MD06');
INSERT INTO Member VALUES ('JD9815', 'Manisha', 'Rajkot', 'Rajkot', 'UK', '1', '1294.00', '5489.00', '2854.00', '18547.00', 'GFSGERS', 'MD03');
INSERT INTO Member VALUES ('JD9802', 'Rimpal', 'Junagdha', 'Junagdha', 'Gondal', '3', '4987.00', '1593.00', '8989.00', '2854.00', 'DDNRDRH', 'MD08');
INSERT INTO Member VALUES ('JD9818', 'Jeram', 'Morbi', 'Morbi', 'Australia', '2', '1593.00', '1593.00', '8989.00', '4987.00', 'NHBGVFC', 'MD05');
INSERT INTO Member VALUES ('JD9821', 'Sumitra', 'Morbi', 'Morbi', 'Australia', '1', '1593.00', '1593.00', '1593.00', '1593.00', 'WERTGDF', 'MD05');
INSERT INTO Member VALUES ('JD9819', 'Astha', 'Bhavanagar', 'Bhavanagar', 'Ahemdabad', '1', '5489.00', '1593.00', '1593.00', '5489.00', 'ZZZZBFV', 'MD10');
INSERT INTO Member VALUES ('JD9805', 'Bhoomi', 'Kalavad', 'Kalavad', 'Ahemdabad', '1', '1593.00', '18547.00', '1593.00', '18547.00', '147-25896312', 'MD02');
INSERT INTO Member VALUES ('JD9807', 'Sejal', 'Bhavanagar', 'Bhavanagar', 'Ahemdabad', '1', '1593.00', '18547.00', '8989.00', '8989.00', 'GHRDWSD', 'MD10');
INSERT INTO Member VALUES ('JD9822', 'Chandni', 'Kalavad', 'Kalavad', 'Ahemdabad', '2', '1593.00', '18547.00', '8989.00', '8989.00', '113-98745678','MD02');
INSERT INTO Member VALUES ('JD9804', 'Merra', 'Morbi', 'Morbi', 'Australia', '1', '4987.00', '5489.00', '1593.00', '1294.00', 'AAAAAAA', 'MD05');
INSERT INTO Member VALUES ('JD9823', 'Monika', 'Rajkot', 'Rajkot', 'UK', '0', '4098.00', '1294.00', '1593.00', '2854.00', 'AAAABAA', 'MD06');
INSERT INTO Member VALUES ('JD9806', 'Ayushui', 'Jammnagar', 'Jammnagar', 'Canada', '1', '10098.00', '1593.00', '1294.00', '18547.00', 'DDDDDDD', 'MD04');
INSERT INTO Member VALUES ('JD9810', 'Kajal', 'Hampshair', 'Hampshair', 'UK', '3', '1294.00', '4098.00', '4987.00', '4987.00', 'MMMMMMM', 'MD09');
INSERT INTO Member VALUES ('JD9817', 'Shilpa', 'Surat', 'Surat', 'Ahemdabad', '2', '5489.00', '4098.00', '2854.00', '8989.00', 'AAAAAAB', 'MD07');
INSERT INTO Member VALUES ('JD9812', 'Priyanka', 'San Jose', 'San Jose', 'Gondal', '1', '4987.00', '1593.00', '8989.00', '2854.00', 'KRFYGJK', 'MD12');
INSERT INTO Member VALUES ('JD9808', 'Dharti', 'Jammnagar', 'Jammnagar', 'Canada', '1', '1593.00', '1593.00', '8989.00', '4987.00', 'HJKORED', 'MD04');
INSERT INTO Member VALUES ('JD9803', 'Monali', 'Jammnagar', 'Jammnagar', 'Canada', '2', '5489.00', '1593.00', '1593.00', '5489.00', 'MJYURFD', 'MD04');
INSERT INTO Member VALUES ('JD9809', 'Bhavika', 'Kalavad', 'Kalavad', 'Ahemdabad', '3', '5489.00', '1593.00', '2854.00', '12098.00', 'Phone No', 'MD02');
INSERT INTO Member VALUES ('JD9814', 'Himankshi', 'Surat', 'Surat', 'Ahemdabad', '2', '5489.00', '18547.00', '1593.00', '12098.00', 'AAAATGF', 'MD01');
INSERT INTO Member VALUES ('JD9816', 'Kiran', 'Surat', 'Surat', 'Ahemdabad', '2', '5489.00', '18547.00', '1593.00', '12098.00', 'JRTVFDD', 'MD07');
INSERT INTO Member VALUES ('JD9811', 'Kajol', 'Bhavanagar', 'Bhavanagar', 'Ahemdabad', '3', '1593.00', '18547.00', '1593.00', '18547.00', 'PPHGRTS', 'MD10');

table requests

CREATE TABLE  "REQUESTS" 
   (
    "ORD_NUM" NUMBER(6,0) NOT NULL PRIMARY KEY, 
	"ORD_AMOUNT" NUMBER(12,2) NOT NULL, 
	"ADVANCE_AMOUNT" NUMBER(12,2) NOT NULL, 
	"ORD_DATE" DATE NOT NULL, 
	"MEMB_CODE" VARCHAR2(6) NOT NULL REFERENCES Member, 
	"MEMBER_CODE" CHAR(6) NOT NULL REFERENCES MEMBERS, 
	"ORD_DESCRIPTION" VARCHAR2(60) NOT NULL
   );
INSERT INTO REQUESTS VALUES('200100', '1098.00', '698.00', '08/01/2021', 'JD9813', 'MD03', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200110', '2854.00', '598.00', '04/15/2021', 'JD9819', 'MD10', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200107', '4598.00', '998.00', '08/30/2021', 'JD9807', 'MD10', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200112', '2098.00', '498.00', '05/30/2021', 'JD9816', 'MD07', 'PAKAINFO'); 
INSERT INTO REQUESTS VALUES('200113', '4098.00', '698.00', '06/10/2021', 'JD9822', 'MD02', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200102', '2098.00', '398.00', '05/25/2021', 'JD9812', 'MD12', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200114', '3598.00', '2098.00', '08/15/2021', 'JD9802', 'MD08', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200122', '2598.00', '498.00', '09/16/2021', 'JD9803', 'MD04', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200118', '598.00', '198.00', '07/20/2021', 'JD9823', 'MD06', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200119', '4098.00', '798.00', '09/16/2021', 'JD9807', 'MD10', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200121', '1598.00', '698.00', '09/23/2021', 'JD9808', 'MD04', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200130', '2598.00', '498.00', '07/30/2021', 'JD9825', 'MD11', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200134', '4298.00', '1898.00', '09/25/2021', 'JD9804', 'MD05', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200108', '4098.00', '698.00', '02/15/2021', 'JD9808', 'MD04', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200103', '1598.00', '798.00', '05/15/2021', 'JD9821', 'MD05', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200105', '2598.00', '598.00', '07/18/2021', 'JD9825', 'MD11', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200109', '3598.00', '898.00', '07/30/2021', 'JD9811', 'MD10', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200101', '2854.00', '1098.00', '07/15/2021', 'JD9801', 'MD08', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200111', '1098.00', '398.00', '07/10/2021', 'JD9820', 'MD08', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200104', '1598.00', '598.00', '03/13/2021', 'JD9806', 'MD04', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200106', '2598.00', '798.00', '04/20/2021', 'JD9805', 'MD02', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200125', '2098.00', '698.00', '10/10/2021', 'JD9818', 'MD05', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200117', '898.00', '298.00', '10/20/2021', 'JD9814', 'MD01', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200987', '598.00', '198.00', '09/16/2021', 'JD9822', 'MD02', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200120', '598.00', '198.00', '07/20/2021', 'JD9809', 'MD02', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200116', '598.00', '198.00', '07/13/2021', 'JD9810', 'MD09', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200124', '598.00', '198.00', '06/20/2021', 'JD9817', 'MD07', 'PAKAINFO'); 
INSERT INTO REQUESTS VALUES('200126', '598.00', '198.00', '06/24/2021', 'JD9822', 'MD02', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200129', '2598.00', '598.00', '07/20/2021', 'JD9824', 'MD06', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200127', '2598.00', '498.00', '07/20/2021', 'JD9815', 'MD03', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200128', '3598.00', '1598.00', '07/20/2021', 'JD9809', 'MD02', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200135', '2098.00', '898.00', '09/16/2021', 'JD9807', 'MD10', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200131', '998.00', '150.00', '08/26/2021', 'JD9812', 'MD12', 'PAKAINFO');
INSERT INTO REQUESTS VALUES('200133', '1298.00', '498.00', '06/29/2021', 'JD9809', 'MD02', 'PAKAINFO');

I hope you get an idea about sample sql database for practice.
I would like to have feedback on my infinityknow.com blog.
Your valuable feedback, question, or comments about this article are always welcome.
If you enjoyed and liked this post, don’t forget to share.

I am Jaydeep Gondaliya , a software engineer, the founder and the person running Pakainfo. I'm a full-stack developer, entrepreneur and owner of Pakainfo.com. I live in India and I love to write tutorials and tips that can help to other artisan, a Passionate Blogger, who love to share the informative content on PHP, JavaScript, jQuery, Laravel, CodeIgniter, VueJS, AngularJS and Bootstrap from the early stage.

Leave a Reply

Your email address will not be published. Required fields are marked *

We accept paid guest Posting on our Site : Guest Post Chat with Us On Skype