SQL Quiz!

[kobmat]

---

Do subqueries work on MYSQL?

[red_colt]

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

ngano gi-group mani bai? wa pa kau ko ka-g sa concept sa group by. nsa d i mahitabo if dili i-group by?

[kobmat]

di ba kinahanglan nimo igroup bai basta naa ka aggregate functions?

[maldito_guapito]

Do subqueries work on MYSQL?

yes subqueries works in mySQL

[kobmat]

unsa na version bai... last time i tried on mysql 3.23 it does not work

[maldito_guapito]

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

ngano gi-group mani bai? wa pa kau ko ka-g sa concept sa group by. nsa d i mahitabo if dili i-group by?

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

bai redcolt,
in the above sql statement, group by is a required clause because it includes an aggregate
function SUM for salary field. Remember it selects two columns dept and salary field is already SUM up meaning the result for salary field is only 1 Row (kay naka sum naman) , with this you need also to GROUP the dept field para 1 row ra pud ang result. If dili naka group ang dept you will encounter an error with the statement.

[maldito_guapito]

unsa na version bai... last time i tried on mysql 3.23 it does not work

im using the GNU version 5 of mySQL (just check mysql.com) . ngita lagi ko higher version pero naay daghan pirated or you have to buy the licenced medyo mahal.

bai we encourage you and ur friend to answer the lastest sql quiz above para ma mag move mi
thanks

[kobmat]

Sakto ni bai?

SELECT MONTH(ORDERS_H.TRANSACTION_DATE) AS MON,
CUSTOMER.CUSTOMER_NAME,
SUM(ORDERS_D.SELLING_PRICE) AS TOTAL_PURCHASES
FROM ORDERS_D, ORDERS_H, CUSTOMER
WHERE ORDERS_D.RECORD_NO = ORDERS_H.RECORD_NO AND
ORDERS_H.CUSTOMER_CODE = CUSTOMER.CUSTOMER_CODE
GROUP BY MON, ORDERS_H.CUSTOMER_CODE;

[maldito_guapito]

Sakto ni bai?

SELECT MONTH(ORDERS_H.TRANSACTION_DATE) AS MON,
CUSTOMER.CUSTOMER_NAME,
SUM(ORDERS_D.SELLING_PRICE) AS TOTAL_PURCHASES
FROM ORDERS_D, ORDERS_H, CUSTOMER
WHERE ORDERS_D.RECORD_NO = ORDERS_H.RECORD_NO AND
ORDERS_H.CUSTOMER_CODE = CUSTOMER.CUSTOMER_CODE
GROUP BY MON, ORDERS_H.CUSTOMER_CODE;

bai kobmat, let have otis validate your answer kay siya man ang ni post the question...Otis asa naman ka ?

part post na sa imong next SQL question..ika number 5 na

[otis]

Sakto ni bai?

SELECT MONTH(ORDERS_H.TRANSACTION_DATE) AS MON,
CUSTOMER.CUSTOMER_NAME,
SUM(ORDERS_D.SELLING_PRICE) AS TOTAL_PURCHASES
FROM ORDERS_D, ORDERS_H, CUSTOMER
WHERE ORDERS_D.RECORD_NO = ORDERS_H.RECORD_NO AND
ORDERS_H.CUSTOMER_CODE = CUSTOMER.CUSTOMER_CODE
GROUP BY MON, ORDERS_H.CUSTOMER_CODE;

Sorry for the late reply, I've been busy sa work.

Yes, I believe that's correct.. pero

GROUP BY MON

wouldn't work in MS SQL Server.

GROUP BY MONTH(ORDERS_H.TRANSACTION_DATE) would work or you could create a derived table having MON as a column ;D