Có 60+ tài liệu thuộc chủ đề "lập trình chuyên nghiệp"
tailieu.vn Xem trực tuyến Tải xuống
SELECT class_nbr, class_size, MIN(room_size) FROM Rooms, Classes. WHERE Classes.class_size <. Rooms.room_size GROUP BY class_nbr, class_size;. You cannot put the other columns in the SELECT list, since it would conflict with the GROUP BY clause. class_nbr class_size MIN(room_size . Rooms.room_size GROUP BY class_nbr, class_size);. Rooms.room_size GROUP BY room_nbr, room_size;. room_nbr room_size MAX(class_size . As it works out, the best fit of...
tailieu.vn Xem trực tuyến Tải xuống
CREATE VIEW Foo1. CREATE VIEW Foo2. But Foo1 is updatable and Foo2 is not. While I know of no formal proof, I suspect that determining whether a complex query resolves to an updatable query for allowed sets of data values possible in the table is an NP-complete problem.. One-to-many joins 8. One-to-many outer joins 9. It is possible for a...
tailieu.vn Xem trực tuyến Tải xuống
This is correct SQL, but it does not work in the old DB2. The compiler apparently tried to insert the VIEW into the FROM clause, as we have seen, but when it expands it out, the results are the same as those of the incorrect first query attempt with a function call in the GROUP BY clause. The trick is...
tailieu.vn Xem trực tuyến Tải xuống
392 CHAPTER 18: VIEWS, DERIVED TABLES, MATERIALIZED TABLES, AND TEMPORARY TABLES. 18.8.1 Using VIEWs. the overhead of building several levels eats up execution time, and the extra storage for materialized VIEW s can be expensive. One of the major uses of VIEW s is security. 18.8.2 Using TEMPORARY TABLEs. The GLOBAL TEMPORARY TABLE can be used to pass data among...
tailieu.vn Xem trực tuyến Tải xuống
19.1.1 Partitioning by Ranges. The best approach to translating a code into a value when ranges are involved is to set up a table with the high and the low values for each translated value in it. This is covered in the chapter on auxiliary tables, Chapter 22, in more detail, but here is a quick review.. Any missing values...
tailieu.vn Xem trực tuyến Tải xuống
WHERE S1.sup <. S2.sup. GROUP BY S1.sup, S2.sup. CREATE TABLE TeamAssignments (player_id INTEGER NOT NULL. SELECT P1.player_id, P2.player_id FROM Players AS P1, Players AS P2 WHERE P1.player_id <. P2.player_id GROUP BY P1.player_id, P2.player_id HAVING P1.player_id + P2.player_id = ALL (SELECT SUM(P3.player_id) FROM TeamAssignments AS P3. WHERE P3.player_id IN (P1.player_id, P2.player_id) GROUP BY P3.team_id);. (SELECT pilot, plane FROM PilotSkills) AS SP1...
tailieu.vn Xem trực tuyến Tải xuống
SELECT (tot_cost - ((tot_qty_on_hand - :order_qty_on_hand. WHERE tot_qty_on_hand >= :order_qty_on_hand);. Find the most recent date that we had enough (or more) quantity on hand to meet the order. If, by dumb blind luck, there is a day when the quantity on hand exactly matched the order, return the total cost as the answer. The CASE expression computes the cost of...
tailieu.vn Xem trực tuyến Tải xuống
Each level is a partition of the level above it. The summary information can be constructed from the level immediately beneath it in the hierarchy.. Look at Chapter 28 on the Nested Sets model for hierarchies.. One trick is to use VIEW s with GROUP BY clauses to build the reporting levels. SELECT region, district, salesman, SUM(sales_sales_amt) FROM Sales. GROUP...
tailieu.vn Xem trực tuyến Tải xuống
442 CHAPTER 21: AGGREGATE FUNCTIONS. Your civics teacher has just asked you to tell her how many people have been President of the United States. The COUNT([ALL] <value expression>) returns the number of members in the <value expression>. The NULL s were thrown away before the counting took place, and an empty set returns zero. The best way to read...
tailieu.vn Xem trực tuyến Tải xuống
SELECT MAX(salary) FROM Personnel UNION. SELECT MAX(salary) FROM Personnel. (SELECT MAX(salary) FROM Personnel WHERE salary. SELECT :search_name, MAX(P1.review_date), P2.review_date FROM Personnel AS P1, Personnel AS P2. WHERE P1.review_date <. P2.review_date AND P1.emp_name = :search_name. AND P2.review_date = (SELECT MAX(review_date) FROM Personnel). GROUP BY P2.review_date;. SELECT DISTINCT salary FROM Personnel WHERE salary >=. (SELECT MAX(salary) FROM Personnel. (SELECT MAX(salary) FROM Personnel...
tailieu.vn Xem trực tuyến Tải xuống
462 CHAPTER 21: AGGREGATE FUNCTIONS. SELECT P2.dept_nbr, MIN(P1.salary_amt) FROM Personnel AS P1, Personnel AS P2 WHERE P1.dept_nbr = P2.dept_nbr. AND P1.salary_amt >= P2.salary_amt GROUP BY P2.dept_nbr, P2.salary_amt HAVING COUNT(DISTINCT P1.salary_amt) <= 3;. 21.4.4 GREATEST() and LEAST() Functions. If you don’t have NULL s in the data:. CASE WHEN col1 >. If you want to return NULL where one of the...
tailieu.vn Xem trực tuyến Tải xuống
I present this version of the query first, because this is how I. (CASE WHEN. The idea is that there are three special cases—all positive numbers, one or more zeros, and some negative numbers in the set. You can find out what your situation is with a quick test on the SIGN() of the minimum value in the set.. You...
tailieu.vn Xem trực tuyến Tải xuống
482 CHAPTER 22: AUXILIARY TABLES. CREATE TABLE Company. (dept_nbr INTEGER NOT NULL,. sequence within department job_descr CHAR(6) NOT NULL,. INSERT INTO Company VALUES (1, 1, 'desc1. INSERT INTO Company VALUES (2, 1, 'desc4. INSERT INTO Company VALUES (3, 1, 'desc10. I am going to use a VIEW rather than a derived table to make the logic in the intermediate step...
tailieu.vn Xem trực tuyến Tải xuống
(CASE WHEN code_type = 'DDC' AND code_value. WHEN code_type = 'ICD' AND code_value. WHEN code_type = 'ISO3166' AND code_value. The size constraint has to be put into the WHEN clause of that second CHECK() constraint, between code_type and code_value.. If you make an error in the code_type or code_description among codes with the same structure, it might not be detected....
tailieu.vn Xem trực tuyến Tải xuống
DELETE FROM CashFlows INSERT INTO CashFlows. scenario 1a: single valued irr DELETE FROM CashFlows. scenario 1b: single valued irr, signs reversed DELETE FROM CashFlows;. scenario 2: double valued irr DELETE FROM CashFlows;. DELETE FROM CashFlows;. scenario 4: undefined irr DELETE FROM CashFlows;. DELETE FROM Test_StraightLine;. DELETE FROM Test_BoundedBox. View results of the test. A computational version of the IRR, due...
tailieu.vn Xem trực tuyến Tải xuống
WHERE P1.occurs. 23.2 The AVG() Function. versus AVG(COALESCE(x as versions of the mean that handle NULL s differently.. A sample needs to use frequencies to adjust the estimate of the mean. The name “mean_p” is to remind us that it is a population mean and not the simple AVG() of the sample data in the table.. 23.3 The Median. The...
tailieu.vn Xem trực tuyến Tải xuống
THEN (P1.weight + MIN(CASE WHEN P1.weight >. P2.weight THEN P1.weight. ELSE NULL END))/2.0 ELSE P2.weight --odd sized table. FROM Parts AS P1, Parts AS P2 GROUP BY P1.weight. HAVING COUNT(CASE WHEN P1.weight >= P2.weight THEN 1. 23.3.8 Celko’s Third Median. Every value in the column weight partitions the table into three sections: values that are less than weight, values that...
tailieu.vn Xem trực tuyến Tải xuống
SELECT S0.salesman, S0.client_name, S0.sales_amt, ((S0.sales_amt * 100)/ ST.salesman_total) AS percent_of_total,. (SUM(S1.sales_amt)/((S0.sales_amt * 100)/. ON (S0.salesman, S0.client_name) <= (S1.salesman, S1.client_name). (SELECT S2.salesman, SUM(S1.sales_amt) FROM Sales AS S2. GROUP BY S2.salesman) AS ST(salesman, salesman_total) ON S0.salesman = ST.salesman. GROUP BY S0.salesman, S0.client_name, S0.sales_amt;. SELECT S0.salesman, S0.client_name, S0.sales_amt. (S0.sales_amt SELECT SUM(S1.sales_amt) FROM Sales AS S1. WHERE S0.salesman = S1.salesman)) AS percentage_of_total,. (SELECT SUM(S3.sales_amt)...
tailieu.vn Xem trực tuyến Tải xuống
542 CHAPTER 23: STATISTICS IN SQL. FROM Personnel GROUP BY race;. FROM Personnel GROUP BY sex;. However, what I wanted was a table with a row for males and a row for females, with columns for each of the racial groups, just as I drew it.. A crosstab can also include other summary data, such as total or average salary...
tailieu.vn Xem trực tuyến Tải xuống
WHERE H3.payment_nbr = H2.payment_nbr + 1 AND H3.paid_on_time <>. SELECT T1.seat_nbr. T2.seat_nbr FROM Theater AS T1, Theater AS T2 WHERE T1.seat_nbr <. T2.seat_nbr. WHERE (T3.seat_nbr BETWEEN T1.seat_nbr AND T2.seat_nbr. 'A') OR (T3.seat_nbr = T2.seat_nbr + 1 AND T3.occupancy_status = 'A') OR (T3.seat_nbr = T1.seat_nbr - 1 AND T3.occupancy_status = 'A'));. The trick here is to look for the starting and...