Hello Everyone This is Gautam, in this post i m going to solve some basic SQL query Practice Set 1
In this post you will learn How to Create Table in SQL and How to Insert Values in The SQL Table,
Note:- All the Given Query Solution is Tested in Microsoft SQL Server Management Studio 18 if you get any wrong query solution then let me know by comments section.
---Practical List - 1
--Salesmen
--SNUM SNAME CITY COMMISSION
-------------------------------------------------------
--1001 Piyush London 12 %
--1002 Sejal Surat 13 %
--1004 Miti London 11 %
--1007 Rajesh Baroda 15 %
--1003 Anand New Delhi 10 %
--SNUM : A unique number assigned to each salesman.
--SNAME : The name of salesman.
--CITY : The location of salesmen.
--COMMISSION: The Salesmen’s commission on orders.
--Solution
USE DEMO_1; --This is database name which is already Created if you don't have created database then follow the steps
create database dtabse; --you can any name instead of dtabse
use dtabse; --after execution this query you can perform all the query
--Step 1: Create a table Salesman and insert all the values
-->
CREATE TABLE SALESMAN(SNUM INT,SNAME VARCHAR(25),CITY VARCHAR(20),COMMISION INT);
-->INSERT ALL THE VALUES
INSERT INTO SALESMAN VALUES(1001,'Piyush','London',12);
INSERT INTO SALESMAN VALUES(1002,'Sejal','Surat',13);
INSERT INTO SALESMAN VALUES(1004,'Mithi','London',11);
INSERT INTO SALESMAN VALUES(1007,'Rajesh','Baroda',15);
INSERT INTO SALESMAN VALUES(1003,'Anand','Delhi',10);
-->Now Check the Values
SELECT * FROM SALESMAN;
-->Create an another table Customers and insert all the values
--Customers
--CNUM CNAME CITY RATING SNUM
-------------------------------------------------------------------
--2001 Harsh London 100 1001
--2002 Gita Rome 200 1003
--2003 Lalit Surat 200 1002
--2004 Govind Bombay 300 1002
--2006 Chirag London 100 1001
--2008 Chinmay Surat 300 1007
--2007 Pratik Rome 100 1004
--CNUM : A unique number assigned to each customer.
--CNAME: The name of the customer.
--CITY : The location of the customer.
--RATING: A level of preference indicator given to this customer.
--SNUM : The number of salesman assigned to this customer.
-->Solution
CREATE TABLE CUSTOMERS(CNUM INT,CNAME VARCHAR(30),CITY VARCHAR(20),RATING INT,SNUM INT);
INSERT INTO CUSTOMERS VALUES(2001,'Harsh','London',100,1001);
INSERT INTO CUSTOMERS VALUES(2002,'Gita','Rome',200,1003),(2003,'Lalit','Surat',200,1002);
INSERT INTO CUSTOMERS VALUES(2004,'Govind','Bombay',300,1002),(2006,'Chirag','London',100,1001);
INSERT INTO CUSTOMERS VALUES(2008,'Chinmay','Surat',300,1007),(2007,'Pratik','Rome',100,1004);
-->Now Check the Values Inserted or Not
SELECT * FROM CUSTOMERS;
--> Create a table Orders and insert all the following Values
--Orders
--ONUM AMOUNT ODATE CNUM SNUM
-------------------------------------------------------
--3001 18.69 10/03/97 2008 1007
--3003 767.19 10/03/97 2001 1001
--3002 1900.10 10/03/97 2007 1004
--3005 5160.45 10/03/97 2003 1002
--3006 1098.16 10/03/97 2008 1007
--3009 1713.23 10/04/97 2002 1003
--3007 75.75 10/04/97 2004 1002
--3008 4723.00 10/05/97 2006 1001
--3010 1309.95 10/06/97 2004 1002
--3011 9891.88 10/06/97 2006 1001
--ONUM : A unique number assigned to each order.
--AMOUNT: The amount of an order.
--ODATE: The date of an order.
--CNUM : The number of customer making the order.
--SNUM : The number of salesman credited with the sale.
-->Solution
CREATE TABLE ORDERS (ONUM INT,AMOUNT FLOAT,ODATE DATE,CNUM INT,SNUM INT);
INSERT INTO ORDERS VALUES
(3001,18.69,'10-MAR-97',2008,1007),
(3003,767.19,'10-MAR-97',2001,1001),
(3002,1900.10,'10-MAR-97',2007,1004),
(3005,5160.45,'10-MAR-97',2003,1002),
(3006,1098.16,'10-MAR-97',2008,1007),
(3009,1713.23,'10-APR-97',2002,1003),
(3007,75.75,'10-MAR-97',2004,1002),
(3008,4723.00,'10-MAY-97',2006,1001),
(3010,1309.95,'10-JUN-97',2004,1002),
(3011,9891.88,'10-JUN-97',2006,1001);
-->Now check the values inserted or not.
SELECT * FROM ORDERS;
THANKS FOR VISITING THIS BLOG,💖
0 Comments