Scribd
Upload
11 views4 pages

SQL Queries for Student and Sales Data

The document outlines the creation and management of two databases: 'cvr' for student records and 'retail' for salespeople and customer data. It includes SQL commands for creating tables, inserting data, and querying information such as student details, customer ratings, and sales orders. Various SQL queries are provided to extract specific information from the databases, such as filtering by city, gender, and scores.

Uploaded by

puzaa7297
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd
11 views4 pages

SQL Queries for Student and Sales Data

The document outlines the creation and management of two databases: 'cvr' for student records and 'retail' for salespeople and customer data. It includes SQL commands for creating tables, inserting data, and querying information such as student details, customer ratings, and sales orders. Various SQL queries are provided to extract specific information from the databases, such as filtering by city, gender, and scores.

Uploaded by

puzaa7297
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as TXT, PDF, TXT or read online on Scribd

1.

Create database:

create database cvr;


2. Connect with database

use cvr;

3. view all the tables in cvr database


show tables;

Student:
Sno
Sname
marks
city
mobile
gender
create table student(sno int, sname varchar(20), marks int, city varchar(20),
mobile int, gender varchar(20));

insert into student values(1,'GUNJA SRIKANTH',90,'Hyderabad',90023223,'male');


insert into student values(2,'DASARI SUPRIYA',34,'Hyderabad',90023223,'female');
insert into student values(3,'VARKALA TEJASRI',50,'Pune',90023223,'female');
insert into student values(4,'LAVOORI VARSHITHA',78,'bglr',90023223,'female');
insert into student values(5,'EDUNURI BHAVIKA',10,'Pune',90023223,'female');
insert into student values(6,'BEJAGAM CHETHAN TEJA',54,'Pune',90023223,'male');
insert into student values(7,'C PRADEEP',95,'Hyderabad',90023223,'male');
insert into student values(8,'RACHAPALLY NIKHITHA',87,'bglr',90023223,'female');
insert into student values(9,'MANDLA NIKHITHA',82,'Hyderabad',90023223,'female');
insert into student values(10,'BATTULA RAM GOPAL',90,'Hyderabad',90023223,'male');
insert into student values(11,'BHUKYA SRIDHAR',77,'Pune',90023223,'male');
insert into student values(12,'SHIVA KUMAR',75,'Pune',90023223,'male');
insert into student values(13,'MOUNIKA',75,'Pune',90023223,'female');

mysql> select * from student;


+------+----------------------+-------+-----------+----------+--------+
| sno | sname | marks | city | mobile | gender |
+------+----------------------+-------+-----------+----------+--------+
| 1 | GUNJA SRIKANTH | 90 | Hyderabad | 90023223 | male |
| 2 | DASARI SUPRIYA | 34 | Hyderabad | 90023223 | female |
| 3 | VARKALA TEJASRI | 50 | Pune | 90023223 | female |
| 4 | LAVOORI VARSHITHA | 78 | bglr | 90023223 | female |
| 5 | EDUNURI BHAVIKA | 10 | Pune | 90023223 | female |
| 6 | BEJAGAM CHETHAN TEJA | 54 | Pune | 90023223 | male |
| 7 | C PRADEEP | 95 | Hyderabad | 90023223 | male |
| 8 | RACHAPALLY NIKHITHA | 87 | bglr | 90023223 | female |
| 9 | MANDLA NIKHITHA | 82 | Hyderabad | 90023223 | female |
| 10 | BATTULA RAM GOPAL | 90 | Hyderabad | 90023223 | male |
| 11 | BHUKYA SRIDHAR | 77 | Pune | 90023223 | male |
| 12 | SHIVA KUMAR | 75 | Pune | 90023223 | male |
| 13 | MOUNIKA | 75 | Pune | 90023223 | female |
+------+----------------------+-------+-----------+----------+--------+

1. Display student details whose city is Pune

ans: select * from student where city='Pune';


2. Display student details whose city is Hyderabad or Pune

ANS: Select * from student where city='Hyderabad' or city='Pune';

3. Display female students from Hyderabad and male students from Pune and all the
students from bglr.

select * from student where gender='female and city='Hyderabad' or gender='male'


and city='Pune' or city=bglr;

4. Display students details


---> from Hyderabad city, male students who scored more than 70 and female who
score more than 50.
---> from Pune only male students

select * from student city='Hyderabad' and gender='male' and marks>=70 or


gender='female' and marks>=50 and city='Hyderabad' or gender='male' and
city='Pune';

5. Display students details whose score between 50 and 80 and city should be
Hyderabad or bglr.

select * from student where marks between 50 and 80 and city in


('Hyderabad','bglr');

[Link] student details whose name starts with S and ends with A

select * from student where sname like "S%A";

[Link] number of students in each city

select city, count(*) as "NO of stds" from students group by city;

[Link] city in education based on score.

select city, avg(marks) as "avg score" from students group by city order by "avg
score" desc limit 1;

[Link] score in each city

select city,max(marks) as top_score from student group by city order by max(marks)


desc;

10. Display toper details in Hyderabad.

select * from student where city='Hyderabad' order by marks desc limit 1;

create database retail;


use retail;

CREATE TABLE salespeople (


snum INT NOT NULL,
sname VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
comm DECIMAL(4,2) NOT NULL,
PRIMARY KEY (snum)
);
INSERT INTO salespeople VALUES (1001, 'Peel', 'London', 0.12);
INSERT INTO salespeople VALUES (1002, 'Serres', 'San Jose', .13);
INSERT INTO salespeople VALUES (1004,'Motika', 'London', .11);
INSERT INTO salespeople VALUES (1007,'Rifkin', 'Barcelona', .15);
INSERT INTO salespeople VALUES (1003,'AxelRod', 'New York', .10);
INSERT INTO salespeople VALUES (1005,'Fran', 'London', .26);
CREATE TABLE customer (
cnum INT NOT NULL,
cname VARCHAR(30) NOT NULL,
city VARCHAR(30) NOT NULL,
rating int not null,
snum int NOT NULL,
PRIMARY KEY (cnum),
FOREIGN KEY (snum) REFERENCES salespeople(snum));

INSERT INTO customer VALUES (2001, 'Hoffman', 'London',100, 1001);


INSERT INTO customer VALUES (2002,'Giovanni', 'Rome', 200, 1003);
INSERT INTO customer VALUES (2003,'Liu','San Jose',200,1002);
INSERT INTO customer VALUES (2004,'Grass', 'Berlin', 300,1002);
INSERT INTO customer VALUES (2006,'Clemens', 'London', 100, 1001);
INSERT INTO customer VALUES(2008,'Cisneros','San Jose',300, 1007);
INSERT INTO customer VALUES (2007,'Pereira', 'Rome', 100 ,1004);

Q1: Find number of customers for each salespeople


Q2: Best Salesperson based on customer rating
Q3: To whom we need to fire from salespeople team.
Q4: Who has less customer rating.
Q5. Display salespeople who has customers from same city.
Q6. Find the names and numbers of all salespeople who had more than one customer.
[Link] customers in San Jose who have a rating above 200.
Q8. List all customers with ratings above San Jose’s average.

CREATE TABLE orders (


onum INT NOT NULL,
amt DECIMAL(7,2) NOT NULL,
odate Date NOT NULL,
cnum int NOT NULL,
PRIMARY KEY (onum),
FOREIGN KEY (cnum) REFERENCES customer(cnum)
);

INSERT INTO orders VALUES (3001, 18.69, '1996-03-10', 2008);


INSERT INTO orders VALUES (3003, 767.19, '1996-10-03', 2001);
INSERT INTO orders VALUES (3002, 1900.10, '1996-10-03', 2007);
INSERT INTO orders VALUES (3005, 5160.45, '1996-10-03', 2003);
INSERT INTO orders VALUES (3006, 1098.16, '1996-10-03', 2008);
INSERT INTO orders VALUES (3009, 1713.23, '1996-10-04', 2002);
INSERT INTO orders VALUES (3007, 75.75, '1996-10-04', 2002);
INSERT INTO orders VALUES (3008, 4723.00, '1996-10-05', 2006);
INSERT INTO orders VALUES (3010, 1309.95, '1996-10-06', 2004);
INSERT INTO orders VALUES (3011, 9891.88, '1996-10-06', 2006);

Common questions

Powered by AI

The SQL query is: `SELECT sname, snum FROM salespeople WHERE snum IN (SELECT snum FROM customer GROUP BY snum HAVING COUNT(*) > 1);`. This reveals that salesperson 'Peel' (snum 1001) handles more than one customer, indicating a greater customer network.

The query to display all customers with ratings above San Jose's average is: `SELECT cname FROM customer WHERE rating > (SELECT AVG(rating) FROM customer WHERE city='San Jose');`. Based on the dataset, the customers 'Cisneros' and 'Grass' have ratings above the average rating in San Jose.

Use the query: `SELECT city, MAX(marks) AS top_score FROM student GROUP BY city ORDER BY city;`. The output will show 'Hyderabad' with 95, 'Pune' with 77, and 'bglr' with 87 as the top scores in each city.

The SQL query to find the best city in education based on student scores is: `SELECT city, AVG(marks) AS "avg score" FROM student GROUP BY city ORDER BY "avg score" DESC LIMIT 1;`. Based on the dataset, Hyderabad would be identified as the best city in education due to its highest average score.

To find the salesperson with the highest customer rating, you can use the following SQL query: `SELECT sname FROM salespeople WHERE snum IN (SELECT snum FROM customer GROUP BY snum ORDER BY SUM(rating) DESC LIMIT 1);`. The salesperson with the highest customer ratings in the dataset is 'Giovanni' who is served by salesperson with snum 1003.

To determine the number of unique customers per salesperson, use the SQL query: `SELECT snum, COUNT(DISTINCT cnum) FROM customer GROUP BY snum;`. For the dataset, the results show: snum 1001 has 2 customers, snum 1002 has 3 customers, and snum 1003 has 1 customer.

To find salespeople who have customers from their own city, the query would be: `SELECT sname FROM salespeople WHERE city IN (SELECT city FROM customer WHERE salespeople.snum = customer.snum);`. For the given dataset, the salesperson 'Rifkin' from Barcelona matches this condition because they have customers from the same city.

The SQL query to list students from Pune is: `SELECT * FROM student WHERE city='Pune';`. The results from the dataset would be: VARKALA TEJASRI, EDUNURI BHAVIKA, BEJAGAM CHETHAN TEJA, BHUKYA SRIDHAR, SHIVA KUMAR, and MOUNIKA.

To determine which salesperson should be fired, we could look at metrics such as the number of customers managed by each salesperson or their effectiveness based on customer ratings. However, the data provides a suggestion to fire based on whether a salesperson is linked to any orders or customer ratings. Therefore, you could query for salespeople with the lowest customer ratings or the fewest orders. For instance, `SELECT sname FROM salespeople WHERE snum NOT IN (SELECT DISTINCT snum FROM customer);` If all salespeople have at least one customer, further analysis is needed on the orders they are associated with for any inference on who should be fired.

The SQL query to retrieve the top-performing student in Hyderabad would be: `SELECT * FROM student WHERE city='Hyderabad' ORDER BY marks DESC LIMIT 1;`. According to the data, the top-performing student in Hyderabad is C PRADEEP, with a score of 95.

You might also like