SQL Quiz!

[kobmat]

---

MySQL ra man gud cge gamit nako..

[digitalsuperman]

next napud.

[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;

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

Another way of joining tables and fields is by using table ALIAS. This helps much
in writing faster your scripts kay dili naman ka mo type sa whole table name.

SELECTÂ* Â* MONTH(h.TRANSACTION_DATE) AS MON,
Â* Â* Â* Â* Â* Â* Â* Â*c.CUSTOMER_NAME,
Â* Â* Â* Â* Â* Â* Â* Â*SUM(d.SELLING_PRICE) AS TOTAL_PURCHASESÂ* Â*
FROM Orders_D d, Orders_H h, Customer c
WHEREÂ* Â* d.RECORD_NO = h.RECORD_NO AND
Â* Â* Â* Â* Â* Â* Â* Â*h.CUSTOMER_CODE = c.CUSTOMER_CODEÂ* Â*
GROUP BY MON, h.CUSTOMER_CODE;

In SQL (ANSI) you can also use JOIN ON and USING as your connectors eliminating the WHERE CLAUSE

Using ON:

SELECTÂ* Â* Â*MONTH(h.TRANSACTION_DATE) AS MON,
Â* Â* Â* Â* Â* Â* Â* Â* Â*c.CUSTOMER_NAME,
Â* Â* Â* Â* Â* Â* Â* Â* Â*SUM(d.SELLING_PRICE) AS TOTAL_PURCHASESÂ* Â*
FROMÂ* Â* Â* Â* Orders_D d JOIN Orders_H ON (d.RECORD_NO = h.RECORD_NO)
Â* Â* Â* Â* Â* Â* Â* Â* Â*AND Customer c JOIN Orders_D d ON (h.CUSTOMER_CODE = c.CUSTOMER_CODE)
GROUP BY MON, h.CUSTOMER_CODE;

Using:

SELECTÂ* Â* Â*MONTH(h.TRANSACTION_DATE) AS MON,
Â* Â* Â* Â* Â* Â* Â* Â* Â*c.CUSTOMER_NAME,
Â* Â* Â* Â* Â* Â* Â* Â* Â*SUM(d.SELLING_PRICE) AS TOTAL_PURCHASESÂ* Â*
FROMÂ* Â* Â* Â* Orders_D d JOIN Orders_H USING (RECORD_NO)
Â* Â* Â* Â* Â* Â* Â* Â* Â*AND Customer c JOIN Orders_D d USING (CUSTOMER_CODE)
GROUP BY MON, h.CUSTOMER_CODE;

*** The MONTH() function works in MSSQL and mySQL but not in Oracle. A function TO_CHAR
is used to change and return the desired format.

SELECT TO_CHAR(h.TRANSACTION_DATE,'MM') AS MON,

-- 'MM' stands for numeric month ex. May - 05, June - 06

[maldito_guapito]

In behalf of Kombat.

SQL Quiz no.5

Assume the following 2 table structures.

Table: Employee Tableepartment
Emp_id Dept_id
Fname Dep_Name
Lname Dep_Head
Dept_id
Salary

What is the SQL statement to display ALL records found in the Employees Table
and records in Department Table that has matched records in Employees Table?

[kobmat]

Thank you bai.. naghimo pa man gud ko ug SQL Question na nindot...

[maldito_guapito]

Thank you bai.. naghimo pa man gud ko ug SQL Question na nindot...

cge pardz i post ang ika number 6 many thanks

[kobmat]

Cge pre.. humanon sa nako pre.. di man kaau ko expert SQL gud..

[kobmat]

In behalf of Kombat.

SQL Quiz no.5

Assume the following 2 table structures.

Table: EmployeeÂ* Â* Â* Â* Â* Â* Tableepartment
Emp_idÂ* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â*Dept_id
FnameÂ* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Dep_Name
LnameÂ* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Â* Dep_Head
Dept_id
Salary

What is the SQL statement to display ALL records found in the Employees Table
and records in Department Table that has matched records in Employees Table?

Wala ko kasabut sa question pre?

pero i will try and answer..

SELECT * FROM Employee WHERE deptid IN (SELECT * FROM Department)

[kobmat]

SQL Quiz No. 6

Table: EmployeeHours
recordid
employeeid
timein (datetime ni cya)
timeout (datetime ni cya)

Table: Employees
employeeid
firstname
lastname
rateperhour

I want to query the number of hours the employees spend working this week and the salary for this week presented neatly on with the following columns

EmployeeName (in the format lastname comma firstname "Osmeña, Tomas")
Hours
Rateperhour
Salary

Wala ra ba ko idea unsaun ni.. still trying to solve this one.. hehehe

[silent-kill]

select lastname, firstname, sum(date(timeout)-date(timein)) as totaltime, rateperhour From emplooyeehours, employees where Employees.employeeid = EployeeHours.employeeid

ambot lang kong sakto ba pero i think after makuha nimo ang sum(date(timeout)-date(timein)) i convert pa nimo into hrs. kai date man ang result ani.. then convertedhr * rateperhour = salary