tailieunhanh - SQL VISUAL QUICKSTART GUIDE- P48

SQL VISUAL QUICKSTART GUIDE- P48:SQL (pronounced es-kyoo-el) is the standard programming language for creating, updating, and retrieving information that is stored in databases. With SQL, you can turn your ordinary questions (“Where do our customers live?”) into statements that your database system can understand (SELECT DISTINCT city, state FROM customers;) | Chapter 15 PostgreSQL The function date_part extracts the specified part of a datetime. current_timestamp returns the current system date and time. The standard addition and subtraction operators add and subtract time intervals from a date. Subtracting one date from another yields the number of days between them Listing . Tip An alternative to date_part is extract . Listing Working with dates in PostgreSQL. Listing -- Extract parts of the current datetime. SELECT date_part second current_timestamp AS sec_pt date_part minute current_timestamp AS min_pt date_part hour current_timestamp AS hr_pt date_part day current_timestamp AS day_pt date_part month current_timestamp AS mon_pt date_part year current_timestamp AS yr_pt -- Add or subtract days months and years. SELECT pubdate INTERVAL 2 DAY AS p2d pubdate - INTERVAL 2 DAY AS m2d pubdate INTERVAL 2 MONTH AS p2m pubdate - INTERVAL 2 MONTH AS m2m pubdate INTERVAL 2 YEAR AS p2y pubdate - INTERVAL 2 YEAR AS m2y FROM titles Working with Dates WHERE title_id T05 -- Count the days between two dates. SELECT date2 - datel AS days FROM SELECT pubdate as datel FROM titles WHERE title_id T05 tl SELECT pubdate as date2 FROM titles WHERE title_id T06 t2 -- Count the months between two dates. SELECT date_part year date2 12 date_part month date2 - date_part year date1 12 date_part month date1 AS months FROM SELECT MIN pubdate AS date1 MAX pubdate AS date2 FROM titles t1 450 SQL Tricks emp_id sales E07 300 E08 400 E03 500 E04 500 E06 500 E01 600 E05 700 E10 700 E02 800 E09 900 Figure The table empsales sorted by ascending sales. Listing Calculate the median of sales in standard SQL. Listing SELECT AVG sales AS median FROM SELECT FROM empsales el empsales e2 GROUP BY HAVING SUM CASE WHEN THEN 1 ELSE 0 END ABS SUM SIGN tl Calculating a Median The median describes the center of the data as the middle point of n sorted values. If n is odd the median is the .