SQL Παραδείγματα
-
Upload
theodore-pallas -
Category
Documents
-
view
38 -
download
0
description
Transcript of SQL Παραδείγματα
Άσκηση
• Customer (cust, name, address)
• Product (prod, name, price)
• Sales(cust, prod, day, month, year, state, amount)
Άσκηση
1. Βρες όλα τα ονόματα των πελατών
2. Βρες τα ονόματα των προϊόντων με τιμή > 10 $
3. Βρες τους κωδικούς των πελατών που αγόρασαν κάποιο προϊόν την 25/11/2001
4. Για κάθε προϊόν δείξε το μέσο όρο πωλήσεων για κάθε μήνα του 2001
5. Δείξε για κάθε πελάτη το σύνολο των πωλήσεων του στη «ΝΥ» στην «CA»,και στο «NJ»
6. Δείξε για κάθε πελάτη το σύνολο των πωλήσεων του στη «ΝΥ» και στην «CA», μόνο αν ο μέσος όρος των πωλήσεων στη «ΝΥ» είναι μεγαλύτερος από το μέσο όρο στην «CA».
7. Για κάθε προϊόν και για τις πωλήσεις του 2001, δείξε το σύνολο των πωλήσεων του προϊόντος κάθε μήνα σαν ποσοστό του ετήσιου συνόλου του
ΛύσηSELECT name
FROM Customer
SELECT name
FROM Product
WHERE price > 10
SELECT C.cust
From Customer C, Sales S
WHERE C.cust=S.cust AND S.day=25 AND S.month=11 AND S.year=2001
ΛύσηSELECT prod, month, avg(amount)
FROM Sales
WHERE Year = 2001
GROUP BY prod, month
SELECT X.cust, sum(X.amount), sum(Y.amount), sum(Z.amount)
FROM Sales X, Sales Y, Sales Z
WHERE X.cust = Y.cust AND Y.cust=Z.cust AND X.state=‘NY’ AND
Y.state=‘CA’ AND Z.state=‘NJ’
GROUP BY X.cust
Λύση
CREATE VIEW Q1(cust, sum_x, sum_y, avg_x, avg_y) as
SELECT X.cust, sum(X.amount), sum(Y.amount), avg(X.amount), avg(Y.amount)
FROM sales X, sales Y
WHERE X.cust=Y.cust and X.state='NY' and Y.state='CA'
GROUP BY X.cust
SELECT cust, sum_x, sum_y from Q1
WHERE avg_x > avg_y
Λύσηcreate view V1 (cust, month, sum_amount) as select cust, month, sum(amount)from saleswhere year=2001group by cust, month
create view V2(cust, sum_amount) as select cust, sum(amount)from saleswhere year=2001group by cust
select V1.cust, V1.month, V1.sum_amount/V2.sum_amountfrom V1, V2where V1.cust=V2.cust
Άσκηση
• Emp (eid integer, ename string, age integer, salary real)• Works (eid integer, did integer, pct_time integer)• Dept(did integer, budget real, managerid integer)
SQL - 1
SELECT E.Ename, E.ageFROM Emp E, Works W1, Works W2WHERE E.eid = W1.eid AND W1.did = 4 AND
E.eid = W2.eid AND W2.did = 5
Άσκηση
• Suppliers (sid:integer, sname:string, address:string)• Parts (pid:integer, pname:string, color:string)• Catalog (sid:integer, pid:integer, cost: real)
Άσκηση
• Έστω ότι έχετε την ακόλουθη περιγραφή ενός πίνακα:
Sales(cust_id int, prod_id int , day int, month int, year int, amount real)
• Γράψτε μία stored procedure (με δύο τρόπους) η οποία να παίρνει σαν παράμετρο τον αριθμό πελάτη και να υπολογίζει το σύνολο των αγορών του το μήνα Ιούνιο του 2001.
• Γράψτε ένα Java πρόγραμμα που να κάνει το ίδιο.
Λύση 1CREATE PROCEDURE Temp1 @customer int AS
SELECT sum(amount)
FROM Sales
WHERE cust_id = @customer AND month=6 AND year=2001
-------
EXECUTE Temp1 1
EXEC Temp1 2
EXEC Temp1 @customer=1
Λύση 2CREATE PROCEDURE Temp2 @customer int AS
DECLARE @cust_id int, @month int, @year int, @amount float DECLARE @total floatSELECT @total=0
DECLARE temp_cursor CURSOR FOR SELECT cust_id, month, year, amountFROM Sales OPEN temp_cursor FETCH NEXT FROM temp_cursor INTO @cust_id, @month, @year, @amount WHILE @@FETCH_STATUS = 0BEGIN
IF (@customer = @cust_id AND @month=6 AND @year=2001) SET @total = @total + @amount
FETCH NEXT FROM temp_cursor INTO @cust_id, @month, @year, @amount
END
PRINT @totalCLOSE temp_cursorDEALLOCATE temp_cursor
Λύση 3import java.io.*;import java.text.*;import java.util.*;import java.sql.*;
public class example {
public static void main(String args[]) {
Connection dbcon; float total, amount; int month, year, cust; String url = "jdbc:odbc:salesdb"; Class.forName("sun.jdbc.odbc.JdbcOdbcDriver"); dbcon = DriverManager.getConnection(url,"sa", ""); Statement stmt; ResultSet rs; stmt = dbcon.createStatement(); rs = stmt.executeQuery("SELECT * FROM Sales"); while (rs.next()) { cust=rs.getInt(“cust_id”); month=rs.getInt(“month”); year=rs.getInt(“year”); amount=rs.getFloat(“amount”); if (cust==2 && month==6 && year==2001) total+=amount; } rs.close(); System.out.println(total); stmt.close(); dbcon.close();}}