Database Day 08: Subquery

1. Simple subquery

Simple subquery doesn't use values from the outer query and is being calculated only once [1]:

SELECT StuID, StuFName, StuLName
FROM Student
WHERE StuID IN
(SELECT StuID
FROM Unit
WHERE UnitName= 'Database');

2. Derived Table/Inline-View subquery


3. Correlated subquery

"Correlated Subquery is a sub-query that uses values from the outer query. In this case, the inner query has to be executed for every row of the outer query." [1]

e.g.  Retrieve one instance of each employee's first and last name for which the bonus in the SalesPerson table is 5000 and for which the employee identification numbers match in the Employee and SalesPerson tables [2].

SELECT DISTINCT c.LastName, c.FirstName, e.BusinessEntityID
FROM Person.Person AS c JOIN HumanResources.Employee AS e
ON e.BusinessEntityID = c.BusinessEntityID
WHERE 5000.00 IN
    (SELECT Bonus
    FROM Sales.SalesPerson sp
    WHERE e.BusinessEntityID = sp.BusinessEntityID) ;

or

e.g. Find the Nth max salary by Co-related Sub-query [1].

SELECT Salary
FROM Employee E1
WHERE N-1 = (SELECT COUNT(*)
             FROM Employee E2
             WHERE E1.salary <E2.Salary)

References
[1] http://stackoverflow.com/questions/17268848/difference-between-subquery-and-correlated-subquery
[2] https://technet.microsoft.com/en-us/library/ms187638(v=sql.105).aspx

Data communications Day 03

1. What is bit?

• The bit is a basic unit of information in computing and digital communications. A bit can have only one of two values, and may therefore be physically implemented with a two-state device [1].

2. What is byte?

• The byte is a unit of information that consists of eight bits.

3. What is digital information?

• Digital information is a type of information stored using a series of ones and zeros, according to TechTerms.com. It is the most commonly used method of storing and reading data, as it can be copied, edited and moved without losing any quality.

4. What is bit rate?

• The number of bits per second that can be transmitted along a digital network.

5. What is bandwidth?

• The maximum bit rate that information can be transferred (commonly measured in bits/second)
• A range of frequencies within a given band, in particular that used for transmitting a signal.
• Bandwidth refers to the amount of information that something, like a connection to the Internet, can handle in a given time.
• Bandwidth is also defined as the amount of data that can be transmitted in a fixed amount of time.

6. What is transmission rate?

• The transmission rate is the rate in bits per second (or kilobits, megabits or gigabits per second) that data can be transmitted.

7. What is latency?

• The delay between the sender and the receiver that receives the decoded message (data), it is the function of the signals travel time, and the processing time at any nodes between them.
• Network latency refers to the time it takes for a packet of data to get from one point to another. In some cases, latency is measured by sending a packet that is returned to the sender; the round-trip time is considered the latency.

8. What is throughput?

• The actual rate that information (data) is transferred.

9. What is error rate?

• The number of corrupted bits expressed as a percentage or fraction of the total sent.

References
1. https://en.wikipedia.org/wiki/Bit

SQL Examples: in, case when then, inline view, over(partition by)

select department_id, employee_id, first_name, last_name,

case

when hire_date = first_hire and hire_date = last_hire

then 'Shortest and Longest Serving'

when hire_date = first_hire then 'Longest Serving'

when hire_date = last_hire then 'Shortest Serving'

end Department_Tenure

from (

select department_id, employee_id, first_name, last_name, hire_date,

max(hire_date) over(partition by department_id) first_hire,

min(hire_date) over(partition by department_id) last_hire

from hr.employees

) Hire_Dates

where hire_date in (first_hire, last_hire)

order by department_id, department_tenure;

References:
G Allen, B Bryla, D Kuhn, and C Allen, "Oracle SQL Recipes A Problem-Solution Approach", 1st ed, Apress, 2009.

Tasks of instructors (lecturers)

Tasks of instructors (lecturers)

Figure: Tasks of instructors (lecturers)