SQLize Online / PHPize Online  /  SQLtest Online

A A A
Share      Blog   Popular
Copy Format Clear
CREATE TABLE salesman ( salesmanid INT, name VARCHAR(255), city VARCHAR(255), commision FLOAT ); -- insert some values INSERT INTO salesman (salesmanid, name, city, commision) VALUES (5001, "james hoog", "new york", .15); INSERT INTO salesman (salesmanid, name, city, commision) VALUES (5002, "nail knite", "paris", .13); INSERT INTO salesman (salesmanid, name, city, commision) VALUES (5005, "pit alex", "london", .11); INSERT INTO salesman (salesmanid, name, city, commision) VALUES (5006, "mc lyon", "paris", .14); INSERT INTO salesman (salesmanid, name, city, commision) VALUES (5007, "paul adam", "rome", .13); INSERT INTO salesman (salesmanid, name, city, commision) VALUES (5003, "lauson hen", "san jose", .12); CREATE TABLE customer ( customerid INT, custname VARCHAR(255), city VARCHAR(255), grade INT, first_puchase DATE, salesmanid INT ); INSERT INTO customer (customerid, custname, city, grade, first_puchase, salesmanid) VALUES (3002, "Nick Rimando", "new york", 100, '2020-12-17', 5001); INSERT INTO customer (customerid, custname, city, grade, first_puchase, salesmanid) VALUES (3007, "Brad Davis", "new york", 200, '2020-11-10', 5001); INSERT INTO customer (customerid, custname, city, grade, first_puchase, salesmanid) VALUES (3005, "Graham Zusi", "california", 200, '2020-10-19', 5002); INSERT INTO customer (customerid, custname, city, grade, first_puchase, salesmanid) VALUES (3008, "Julian Green", "london", 300, '2020-02-21', 5002); INSERT INTO customer (customerid, custname, city, grade, first_puchase, salesmanid) VALUES (3004, "Fabian Johnson", "paris", 300, '2020-06-07', 5006); INSERT INTO customer (customerid, custname, city, grade, first_puchase, salesmanid) VALUES (3009, "geoff cameron", "berlin", 100, '2020-12-30', 5003); INSERT INTO customer (customerid, custname, city, grade, first_puchase, salesmanid) VALUES (3003, "jozy altidor", "moscow", 200, '2020-09-01', 5007); INSERT INTO customer (customerid, custname, city, grade, first_puchase, salesmanid) VALUES (3001, "BRAD Guzan", "london", 100, '2020-11-29', 5005); INSERT INTO customer (customerid, custname, city, grade, first_puchase, salesmanid) VALUES (3006, "brad Guzan", "london", 100, '2020-06-20', 5005); SELECT * FROM customer; SELECT * FROM salesman; SELECT customer.custname, salesman.salesmanid, salesman.name, salesman.city, salesman.commision FROM customer LEFT JOIN salesman ON customer.salesmanid = salesman.salesmanid; -- Find all records where Customer Grade is greater than 100 and Salesman Commision is greater than .14 SELECT customer.customerid, customer.custname, customer.city, customer.grade, customer.first_puchase, salesman.name, salesman.city, salesman.commision FROM customer LEFT JOIN salesman ON customer.salesmanid = salesman.salesmanid WHERE customer.grade > 100 AND salesman.commision > .14; -- Find all customer records containing the word "brad" in the name, regardless of whether it was stored as BRAD, Brad, brad SELECT * FROM customer WHERE custname LIKE '%brad%'; -- Find the details for the customer who recently make his first purchase. SELECT * FROM customer WHERE first_puchase = (SELECT MAX(first_puchase) FROM customer); -- Find number of salesid for each city in ascending order SELECT city, COUNT(*) AS num_sales FROM customer GROUP BY city ORDER BY num_sales; -- Find number of records from customer table with new York city SELECT COUNT(*) AS NY_city_counts FROM customer WHERE city = 'new york'; -- Find the details for salesman and customer where customer make their first purchase in the month of November SELECT salesman.name as salesman_name, customer.custname FROM salesman, customer WHERE customer.first_puchase BETWEEN '2020-11-01' AND '2020-11-30' AND customer.salesmanid = salesman.salesmanid;
Stuck with a problem? Got Error? Ask ChatGPT!
Copy Clear