Tài liệu SQL Puzzles & Answers- P3 pptx

66 PUZZLE 15 FIND THE LAST TWO SALARIES
AS
SELECT S0.emp_name_id, S0.sal_date AS curr_date,
S0.sal_amt AS curr_amt,
S1.sal_date AS prev_date,
S1.sal_amt AS prev_amt
FROM Salaries AS S0
LEFT OUTER JOIN
Salaries AS S1
ON S0.emp_name_id = S1.emp_name_id
AND S0.sal_date > S1.sal_date;
then use it in a self-join query:
SELECT S0.emp_name_id, S0.curr_date, S0.curr_amt,
S0.prev_date, S0.prev_amt
FROM SalaryHistory AS S0
WHERE S0.curr_date
= (SELECT MAX(curr_date)
FROM SalaryHistory AS S1
WHERE S0.emp_name_id = S1.emp_name_id)
AND (S0.prev_date
= (SELECT MAX(prev_date)
FROM SalaryHistory AS S2
WHERE S0.emp_name_id = S2.emp_name_id)
OR S0.prev_date IS NULL)
This is still complex, but that view might be useful for computing
other statistics.
Answer #7
Here is another version of the VIEW approach from MarkC600 on the
SQL Server Newsgroup. The
OUTER JOIN has been replaced with a
RANK() function from SQL:2003. Study this and see how the thought
pattern is changing:
WITH SalaryRanks(emp_name, sal_date, sal_amt, pos)
AS
(SELECT emp_name, sal_date, sal_amt,
RANK() OVER(PARTITION BY emp_name ORDER BY sal_date
DESC)
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PUZZLE 15 FIND THE LAST TWO SALARIES 67
FROM Salaries)
SELECT C.emp_name,
C.sal_date AS curr_date, C.sal_amt AS curr_amt,
P.sal_date AS prev_date, P.sal_amt AS prev_amt
FROM SalaryRanks AS C
LEFT OUTER JOIN
SalaryRanks AS P
ON P.emp_name = C.emp_name
AND P.pos = 2
WHERE C.pos = 1;
Answer #8
Here is an SQL:2003 version, with OLAP functions and SQL-92 CASE
expressions from Dieter Noeth:
SELECT S1.emp_name,
MAX (CASE WHEN rn = 1 THEN sal_date ELSE NULL END) AS
curr_date,
MAX (CASE WHEN rn = 1 THEN sal_amt ELSE NULL END) AS
curr_amt,
MAX (CASE WHEN rn = 2 THEN sal_date ELSE NULL END) AS
prev_date,
MAX (CASE WHEN rn = 2 THEN sal_amt ELSE NULL END) AS
prev_amt,
FROM (SELECT emp_name, sal_date, sal_amt,
RANK()OVER (PARTITION BY S1.emp_name ORDER BY
sal_date DESC)
FROM Salaries) AS S1 (emp_name, sal_date, sal_amt,
rn)
WHERE rn < 3
GROUP BY S1.emp_name;
The idea is to number the rows within each employee and then to
pull out the two most current values for the employment date. The other
approaches build all the target output rows first and then find the ones
we want. This query finds the raw rows first and puts them together last.
The table is used only once, no self-joins, but a hidden sort will be
required for the
RANK() function. This is probably not a problem in SQL
engines that use contiguous storage or have indexing that will group the
employee names together.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
68 PUZZLE 15 FIND THE LAST TWO SALARIES
Answer #9
Here is another answer from Dieter Noeth using OLAP/CTE (tested on
Teradata, but runs on MS-SQL 2005, too):
WITH CTE (emp_name, sal_date, sal_amt, rn)
AS
(SELECT emp_name, sal_date, sal_amt ,
ROW_NUMBER() OVER (PARTITION BY emp_name
ORDER BY sal_date DESC) AS rn – row numbering
FROM Salaries)
SELECT O.emp_name,
O.sal_date AS curr_date, O.sal_amt AS curr_amt,
I.sal_date AS prev_date, I.sal_amt AS prev_amt
FROM CTE AS O
LEFT OUTER JOIN
CTE AS I
ON O.emp_name = I.emp_name AND I.rn = 2
WHERE O.rn = 1;
Again, SQL:2003 using OLAP functions in Teradata:
SELECT emp_name, curr_date, curr_amt,
prev_date, prev_amt
FROM (SELECT emp_name,
sal_date AS curr_date, sal_amt AS curr_amt,
MIN(sal_date)
OVER (PARTITION BY emp_name
ORDER BY sal_date DESC
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
AS prev_date,
MIN(sal_amt)
OVER (PARTITION BY emp_name
ORDER BY sal_date DESC
ROWS BETWEEN 1 FOLLOWING AND 1 FOLLOWING)
AS prev_amt,
ROW_NUMBER() OVER (PARTITION BY emp_name ORDER BY
sal_date DESC) AS rn
FROM Salaries) AS DT
WHERE rn = 1;
This query would be easier if Teradata supported the WINDOW clause.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PUZZLE 16 MECHANICS 69
PUZZLE
16 MECHANICS
Gerard Manko at ARI posted this problem on CompuServe in April
1994. ARI had just switched over from Paradox to Watcom SQL (now
part of Sybase). The conversion of the legacy database was done by
making each Paradox table into a Watcom SQL table, without any
thought of normalization or integrity rules—just copy the column
names and data types. Yes, I know that as the SQL guru, I should have
sent him to that ring of hell reserved for people who do not normalize,
but that does not get the job done, and ARI’s approach is something I
find in the real world all the time.
The system tracks teams of personnel to work on jobs. Each job has a
slot for a single primary mechanic and a slot for a single optional
assistant mechanic. The tables involved look like this:

CREATE TABLE Jobs
(job_id INTEGER NOT NULL PRIMARY KEY,
start_date DATE NOT NULL,
);
CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
emp_name CHAR(20) NOT NULL,
);

CREATE TABLE Teams
(job_id INTEGER NOT NULL,
mech_type INTEGER NOT NULL,
emp_id INTEGER NOT NULL,
);

Your first task is to add some integrity checking into the Teams table.
Do not worry about normalization or the other tables for this problem.
What you want to do is build a query for a report that lists all the jobs
by
job_id, the primary mechanic (if any), and the assistant mechanic (if
any). Here are some hints: You can get the
job_ids from Jobs because
that table has all of the current jobs, while the
Teams table lists only
those jobs for which a team has been assigned. The same person can be
assigned as both a primary and assistant mechanic on the same job.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
70 PUZZLE 16 MECHANICS
Answer #1
The first problem is to add referential integrity. The Teams table should
probably be tied to the others with
FOREIGN KEY references, and it is
always a good idea to check the codes in the database schema, as
follows:
CREATE TABLE Teams
(job_id INTEGER NOT NULL REFERENCES Jobs(job_id),
mech_type CHAR(10) NOT NULL
CHECK (mech_type IN ('Primary', 'Assistant')),
emp_id INTEGER NOT NULL REFERENCES Personnel(emp_id),
);

Experienced SQL people will immediately think of using a LEFT
OUTER JOIN
, because to get the primary mechanics only, you could
write:

SELECT Jobs.job_id, Teams.emp_id AS “primary”
FROM Jobs LEFT OUTER JOIN Teams
ON Jobs.job_id = Teams.job_id
WHERE Teams.mech_type = 'Primary';

You can do a similar OUTER JOIN to the Personnel table to tie it to
Teams, but the problem here is that you want to do two independent
outer joins for each mechanic’s slot on a team, and put the results in one
table. It is probably possible to build a horrible, deeply nested self
OUTER
JOIN
all in one SELECT statement, but you would not be able to read or
understand it.
You could do the report with views for primary and assistant
mechanics, and then put them together, but you can avoid all of this
mess with the following query:

SELECT Jobs.job_id,
(SELECT emp_id
FROM Teams
WHERE Jobs.job_id = Teams.job_id
AND Teams.mech_type = 'Primary') AS "primary",
(SELECT emp_id
FROM Teams
WHERE Jobs.job_id = Teams.job_id
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PUZZLE 16 MECHANICS 71
AND Teams.mech_type = 'Assistant') AS assistant
FROM Jobs;

The reason that “primary” is in double quotation marks is that it is a
reserved word in SQL-92, as in
PRIMARY KEY. The double quotation
marks make the word into an identifier. When the same word is in single
quotation marks, it is treated as a character string.
One trick is the ability to use two independent scalar
SELECT
statements in the outermost
SELECT. To add the employee’s name,
simply change the innermost
SELECT statements.

SELECT Jobs.job_id,
(SELECT name
FROM Teams, Personnel
WHERE Jobs.job_id = Teams.job_id
AND Personnel.emp_id = Teams.emp_id
AND Teams.mech_type = 'Primary') AS “primary",
(SELECT name
FROM Teams, Personnel
WHERE Jobs,job_id = Teams,job_id
AND Personnel.emp_id = Teams.emp_id
AND Teams.mech_type = 'Assistant') AS Assistant
FROM Jobs:

If you have an employee acting as both primary and assistant
mechanic on a single job, then you will get that employee in both slots. If
you have two or more primary mechanics or two or more assistant
mechanics on a job, then you will get an error, as you should. If you have
no primary or assistant mechanic, then you will get an empty
SELECT
result, which becomes a
NULL. That gives you the outer joins you wanted.
Answer #2
Skip Lees of Chico, California, wanted to make the Teams table enforce
the rules that:
1. A
job_id has zero or one primary mechanics.
2. A
job_id has zero or one assistant mechanics.
3. A
job_id always has at least one mechanic of some kind.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
72 PUZZLE 16 MECHANICS
Based on rule 3, there should be no time at which a job has no team
members. On the face of it, this makes sense.
Therefore, team information will have to be entered before job
records. Using a referential integrity constraint will enforce this
constraint. Restrictions 1 and 2 can be enforced by making “
job_id” and
“
mech_type” into a two-column PRIMARY KEY, so that a job_id could
never be entered more than once with a given
mech_type.
CREATE TABLE Jobs
(job_id INTEGER NOT NULL PRIMARY KEY REFERENCES Teams
(job_id),
start_date DATE NOT NULL,
);
CREATE TABLE Teams
(job_id INTEGER NOT NULL,
mech_type CHAR(10) NOT NULL
CHECK (mech_type IN ('Primary', 'Assistant')),
emp_id INTEGER NOT NULL REFERENCES Personnel(emp_id),

PRIMARY KEY (job_id, mech_type));
There is a subtle “gotcha” in this problem. SQL-92 says that a
REFERENCES clause in the referencing table has to reference a UNIQUE or
PRIMARY KEY column set in the referenced table. That is, the reference is
to be to the same number of columns of the same datatypes in the same
order. Since we have a
PRIMARY KEY, (job_id, mech_type) is available
in the Teams table in your answer.
Therefore, the
job_id column in the Jobs table by itself cannot
reference just the
job_id column in the Teams table. You could get
around this with a
UNIQUE constraint:
CREATE TABLE Teams
(job_id INTEGER NOT NULL UNIQUE,
mech_type CHAR(10) NOT NULL
CHECK (mech_type IN ('Primary', 'Assistant')),
PRIMARY KEY (job_id, mech_type));
but it might be more natural to say:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PUZZLE 16 MECHANICS 73
CREATE TABLE Teams
(job_id INTEGER NOT NULL PRIMARY KEY,
mech_type CHAR(10) NOT NULL
CHECK (mech_type IN ('primary', 'assistant')),
UNIQUE (job_id, mech_type));
because job_id is what identifies the entity that is represented by the
table. In actual SQL implementations, the
PRIMARY KEY declaration can
affect data storage and access methods, so the choice could make a
practical difference in performance.
But look at what we have done! I cannot have both “
primary” and
“
assistant” mechanics on one job because this design would require
job_id to be unique.
Answer #3
Having primary and assistant mechanics is a property of a team on a job,
so let’s fix the schema:
CREATE TABLE Teams
(job_id INTEGER NOT NULL REFERENCES Jobs(job_id),
primary_mech INTEGER NOT NULL
REFERENCES Personnel(emp_id),
assist_mech INTEGER NOT NULL
REFERENCES Personnel(emp_id),
CONSTRAINT at_least_one_mechanic
CHECK(COALESCE (primary_mech, assist_mech) IS NOT NULL),
);

But this is not enough; we want to be sure that only qualified
mechanics hold those positions:
CREATE TABLE Personnel
(emp_id INTEGER NOT NULL PRIMARY KEY,
mech_type CHAR(10) NOT NULL
CHECK (mech_type IN ('Primary', 'Assistant')),
UNIQUE (emp_id, mech_type),
);
So change the Teams again:
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
74 PUZZLE 16 MECHANICS
CREATE TABLE Teams
(job_id INTEGER NOT NULL REFERENCES Jobs(job_id),
primary_mech INTEGER NOT NULL,
primary_type CHAR(10) DEFAULT ‘Primary’ NOT NULL
CHECK (primary_type = ‘Primary’)
REFERENCES Personnel(emp_id, mech_type),
assist_mech INTEGER NOT NULL
assist_type CHAR(10) DEFAULT ‘Assistant’ NOT NULL
CHECK (assist_type = ‘Assistant’)
REFERENCES Personnel(emp_id, mech_type),
CONSTRAINT at_least_one_mechanic
CHECK(COALESCE (primary_mech, assist_mech) IS NOT NULL),
);
Now it should work.
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.
PUZZLE 17 EMPLOYMENT AGENCY 75
PUZZLE
17 EMPLOYMENT AGENCY
Larry Wade posted a version of this problem on the Microsoft ACCESS
forum at the end of February 1996. He is running an employment
service that has a database with tables for job orders, candidates, and
their job skills. He is trying to do queries to match candidates to job
orders based on their skill. The job orders take the form of a Boolean
expression connecting skills. For example, find all candidates with
manufacturing and inventory or accounting skills.
First, let’s construct a table of the candidate’s skills. You can assume
that personal information about the candidate is in another table, but we
will not bother with it for this problem.
CREATE TABLE CandidateSkills
(candidate_id INTEGER NOT NULL,
skill_code CHAR(15) NOT NULL,
PRIMARY KEY (candidate_id, skill_code));
INSERT INTO CandidateSkills
VALUES ((100, 'accounting'),
(100, 'inventory'),
(100, 'manufacturing'),
(200, 'accounting'),
(200, 'inventory'),
(300, 'manufacturing'),
(400, 'inventory'),
(400, 'manufacturing'),
(500, 'accounting'),
(500, 'manufacturing'));
The obvious solution would be to create dynamic SQL queries in a
front-end product for each job order, such as:
SELECT candidate_id, 'job_id #212' constant job id code
FROM CandidateSkills AS C1, one correlation per skill
CandidateSkills AS C2,
CandidateSkills AS C3
WHERE C1.candidate_id = C2.candidate_id
AND C1.candidate_id = C3.candidate_id
AND job order expression created here
Please purchase PDF Split-Merge on www.verypdf.com to remove this watermark.

Xem chi tiết: Tài liệu SQL Puzzles & Answers- P3 pptx