SQL Quiz!

[maldito_guapito]

---

Lingaw lingaw lang fellow progs and devs. this topic is about SQL scripting. everyone is welcome to answer and post on the query. once the question has been answered, anyone can post their contribution only SQL. enjoy!

[maldito_guapito]

For the introductory question.

EMP_ID FNAME LNAME DEPT SALARY

101 TOM CRUISE IT 15000
107 STEVEN PAWIKS FIN 13000
108 MARIE BISCUIT HR 12000
102 MARK BAUTISTA OPRN 20000
103 JOHN LAPUZ IT 11000
105 LANI MISALUCH HR 9000
104 OGGIE DAPOGIE FIN 9500

Considering the table structure and records shown above,
What is the SQL statement in order to produce the result below

DEPT SALARY

IT 26000

LEVEL: Easy

[kyle_nexus]

SELECT dept, SUM(salary) AS Expr1
FROM table1
WHERE (dept = 'IT')
GROUP BY dept

[maldito_guapito]

SELECT dept, SUM(salary) AS Expr1
FROM table1
WHERE (dept = 'IT')
GROUP BY dept

nice kyle! cge pardz share sab

[kyle_nexus]

sakto diay? hehehe

nagtuon pa sab ko bai, taghap ra gani to ako-a.

kaw lang sa ask, kay ikaw man ang quiz master!

[maldito_guapito]

SQL Quiz 2:

PROD_IDÂ* Â*ITEMÂ* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*UNIT_PRICEÂ* Â* Â* Â* Â* STOCK_QTYÂ* Â* Â*

60801Â* Â* Â* Â*CHEEZE BURGERÂ* Â* Â* Â*20.25Â* Â* Â* Â* Â* Â*30
78000Â* Â* Â* Â*SPAGHETTIÂ* Â* Â* Â* Â* Â* Â* Â* 30.00Â* Â* Â* Â* Â* Â* Â*5
98451Â* Â* Â* Â*BURGER STEAKÂ* Â* Â* Â* Â* 35.00Â* Â* Â* Â* Â* Â* 15
35990Â* Â* Â* Â*DIET COKEÂ* Â* Â* Â* Â* Â* Â* Â* 18.00Â* Â* Â* Â* Â* Â* 50

Question: Create a SQL statement to query the product having the highest price.
Level: Easy

Â*

[maldito_guapito]

SQL Quiz 3:

EMP_ID FNAME LNAME DEPT SALARY

101 TOM CRUISE IT 15000
107 STEVEN PAWIKS FIN 13000
108 MARIE BISCUIT HR 12000
102 MARK BAUTISTA OPRN 20000
103 JOHN LAPUZ IT 11000
105 LANI MISALUCH HR 9000
104 OGGIE DAPOGIE FIN 9500

Question: Create a SQL statement to list all employees having a letter "O" in their first name.
Level: Easy

[otis]

number2:
select top 1 * from products order by unit_price desc

number3:
select * from employees where fname like '%O%'

kapoy himo quiz noh? heehee

[otis]

Table name: Customer

CUSTOMER_CODE CUSTOMER_NAME
c1maldito
c2guapito
c3otis
c4kyle
c5nexus


Table name: Product

PRODUCT_CODE PRODUCT_NAME SELLING_PRICE
p1tv5
p2car10
p3bike15
p4house20


Table name: Orders_H

RECORD_NO CUSTOMER_CODE TRANSACTION_DATE
1c101012005
2c201022005
3c302032005
4c402052005
5c402062005
6c502252005


Table name: Orders_D

RECORD_NO PRODUCT_CODE SELLING_PRICE
1p15
1p315
1p420
2p420
2p15
3p210
4p315
4p420
4p210
5p15
5p210
5p315
6p420
6p15
6p210
6p315
6p35


say.. I wanna get info on
sales per month per customer

example result:

MOTH CUSTOMER TOTAL PURCHASES
01otis1000
01kyle2000
02otis2000
02maldito1000

[maldito_guapito]

number2:
select top 1 * from products order by unit_price desc

number3:
select * from employees where fname like '%O%'

kapoy himo quiz noh? heehee

Your answers for both question are ok.
Aside from using the Top N query another way is using a PSEUDOCOLUMN asign a sequential value
and a subquery

SELECT rownum AS RANK,
item, unit_price, stock_qty
FROM (SELECT item, unit_price, stock_qty
FROM table1 ORDER BY unit_price DESC)
WHERE rownum = 1;

you can manipulate the number of Top rows by changing the expression in the WHERE clause.