SQL Quiz!

[kobmat]

---

nya how about the "this week" thing bai... unsaun man nimo Group by ana

[silent-kill]

aw o kalimot ko..
select lastname, firstname, sum(date(timeout)-date(timein)) as totaltime, rateperhour From emplooyeehours, employees where Employees.employeeid = EployeeHours.employeeid and timein >= "$startdate" and timeout <="$enddate"

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

Wala ko kasabut sa question pre?

pero i will try and answer..

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

SQL Quiz 5

Both Employees and Departments tables are related because of the Dept_id field di ba.
Karon unsaon man nimo pag display sa TANANG records naa sa Employees table ug records
sa Department na kato rang naay relation sa Employees table.

For Example below naay 2 employees unya ang ilang dept is only 1 - IT and 2 - Operations
unsaon man nimo pag create sa sql statement to retrieve records na walay apil ang 3 - HR and 4 - Finance from departments table since wala may employees na dept_id is 3 & 4 ?

Table: Employee
Emp_id Fname Lname Dept_id
101 maldits guapits 1
102 mister kombat 2

Tableepartment
Dept_id Dep_name
1 IT
2 Operations
3 HR
4 Finance

[kobmat]

SELECT DISTINCT dept_id FROM Employee;

or

SELECT * FROM Department WHERE Dept_Id IN (SELECT DISTINCT dept_id FROM Employee);

Mao na bai?

[maldito_guapito]

SELECT DISTINCT dept_id FROM Employee;

or

SELECT * FROM Department WHERE Dept_Id IN (SELECT DISTINCT dept_id FROM Employee);

Mao na bai?

You use the LEFT INNER JOIN ; LEFT JOIN

SELECT *
FROM Employee e LEFT JOIN Department d
ON e.Dept_id = d.Dept_id;

or

SELECT *
FROM Employee e LEFT INNER JOIN Department d
USING (Dept_id);

the LEFT INNER JOIN means show ALL records found on the LEFT table (Employee table, ang Right table is Department) and records
having a relation or join Department table (ang Dept_id).

Are result sa query is:
Emp_id Fname Lname Dept_id
101 maldits guapits 1
102 mister kombat 2

If you are going to change it to RIGHT INNER JOIN
the result is:

Emp_id Fname Lname Dept_id Dept_Name

101 maldits guapits 1 IT
102 mister kombat 2 Operations
null null null 3 HR
null null null 4 Finance

[maldito_guapito]

SQL Quiz No. 7

Table:Employees
empidÂ* Â* Â* Â* fnameÂ* Â* Â* Â* Â* Â*lnameÂ* Â* Â* Â* deptidÂ* Â* Â*salary
101Â* Â* Â* Â* Â* Â*AbelÂ* Â* Â* Â* Â* Â* Â* AbellardoÂ* Â*ITÂ* Â* Â* Â* Â* Â* 10000
102Â* Â* Â* Â* Â* Â*JuanÂ* Â* Â* Â* Â* Â* Â*TamaÂ* Â* Â* Â* Â* HRÂ* Â* Â* Â* Â* Â*9000
103Â* Â* Â* Â* Â* Â*PedroÂ* Â* Â* Â* Â* Â*PendukoÂ* Â* Â*FinÂ* Â* Â* Â* Â* Â*10500
104Â* Â* Â* Â* Â* Â*PeterÂ* Â* Â* Â* Â* Â* PandesalÂ* Â* OpÂ* Â* Â* Â* Â* Â* 8000
105Â* Â* Â* Â* Â* Â*MariaÂ* Â* Â* Â* Â* Â* AgwantaÂ* Â* Â*ITÂ* Â* Â* Â* Â* Â* Â*9000

Using a subquery, create a SQL script to display all employees
having salary greater than Maria Agwanta.


SQL Quiz No. 8

Using the same table above create a SQL script that would result

My name isÂ* ABELLARDO, Abel and i am from IT department having a salary of 10000
My name isÂ* TAMAD, Juan and i am from HR department having a salary of 9000
My name isÂ* PENDUKO, Pedro and i am from FIN department having a salary of 10500

so on .........

[zengatsu]

no.7
select * from employees where salary > (select salary from employees where fname = 'Maria' and lname = 'Agwanta')

no.8
select 'My name is ' || upper(lname) || ', ' || fname || ' and i am from ' || deptid || ' department having a salary of ' || salary from employees

[maldito_guapito]

no.7
select * from employees where salary > (select salary from employees where fname = 'Maria' and lname = 'Agwanta')

no.8
select 'My name is ' || upper(lname) || ', ' || fname || ' and i am from ' || deptid || ' department having a salary of ' || salary from employees

sorry for the delay dude. you got it both right!

[maldito_guapito]

Question No. 9

Examine the structure of the EMPLOYEES table.

Employee_IDÂ* Â* NUMBER Primary Key
First_NameÂ* Â* Â* Â*VARCHAR2
Last_NameÂ* Â* Â* Â*VARCHAR2

Which 3 statements inserts a row in the table (choose 3)

a. INSERT INTO employees
VALUES (NULL, 'John','Smith');
b. INSERT INTO employees(first_name, last_name)
VALUES('John','Smith');
c.INSERT INTO employees
VALUES ('1000','John',NULL);
d. INSERT INTO employees(first_name, last_name, employee_id)
VALUES (1000,'John','Smith');
e. INSERT INTO employees(employee_id)
VALUES(1000);
f. INSERT INTO employees(employee_id,first_name,last_name)
VALUES (1000,'John','');

[simoncpu]

a, c, and f? It depends on what you're using, really. Employee_ID has not been declared as NOT NULL. Some database systems allows you to skip entering a value for the primary key as long as it's set to auto-increment or if you've specified a default value (i.e., b). Heck, some even allow you to enclose your values with quotes even if the data type is integer (i.e., c).

:mrgreen:

[ simon.cpu ]