![]() · I have a 20 table database, built around the Applications table. Each Application row will probably have a link to a row in the Organisations table. Each. Read "How to Install MySQL and Get Started" on how to install, customize, and get started with MySQL. Summary of MySQL Commands Used in this Tutorial. Forums SQL Server 2016 SQL Server 2016 - Administration SQL Server 2016 - Development and T-SQL SQL Server 2014 Administration - SQL Server 2014. In this tutorial, you will learn various techniques of how to export MySQL table to CSV file. ![]() ![]() Common My. SQL Queries. Common My. SQL Queries. Aggregates. Aggregates and Statistics. Aggregates from bands of values. Pivot tables (crosstabs)Data comparison. Database metadata. Foreign keys. Primary keys. Show. Date and time. Period arithmetic. Scheduling. Frequencies. Graphs and Hierarchies. JOINJoin or subquery? NULLs. Ordering resultsets. Relational division. Sequences. Spherical geometry. Statistics without aggregates. Stored procedures. Strings. Aggregates are more popular here than any other topic, by more than 2 to 1 over the next most popular. Basic aggregation is the simplest grouping query pattern: for column foo, display the count, the smallest, the largest, the sum, the average or some other statistic of column bar values. For example, the minimum bar value per foo: SELECT foo, MIN(bar) AS bar. Return the highest bar value for each foo, ordering top to bottom by that value: Read the entire item. Last updated 2. 8 Aug 2. You track squash court bookings and fees. A court booking has one fee, but it has two member references, and those can be the same if one member has brought a guest. For each booking row, the fee is to be split between the two members. What query correctly aggregates fees including cases where the two members of record are the same? DROP TABLE IF EXISTS bookings. CREATE TABLE bookings (. NOT NULL. member. NOT NULL. member. NOT NULL. time timestamp NOT NULL. NOT NULL. INSERT INTO bookings ( court_id , member. Read the entire item. Last updated 0. 9 Sep 2. Given a parent table and two child tables, a query which sums values in both child tables, grouping on a parent table column, returns sums that are exactly twice as large as they should be. In this example from the My. SQL General Discussion list: DROP TABLE IF EXISTS package. Item,package. Credit,package. Item. Tax. CREATE TABLE package. Item (. package. Item. ID INT. package. Item. Name CHAR(2. 0). package. Item. Price DECIMAL(1. INSERT INTO package. Item VALUES(1,'Delta Hotel',1. CREATE TABLE package. Credit (. package. Credit. ID INT. package. Credit. Item. ID INT. Read the entire item. Last updated 2. 2 Feb 2. You have a table of grouped ranks .. DROP TABLE IF EXISTS grps,ranks. CREATE TABLE grps (grp int). INSERT INTO grps VALUES(1),(2),(3),(4). CREATE TABLE ranks(grp int,rank int). INSERT INTO ranks VALUES(1, 4 ),(1, 7 ),(1, 9 ),(2, 2 ),(2, 3 ),(2, 5 ),(2, 6 ),(2, 8 ),(3, 1 ),(4,1. The simplest query for group leaders is .. Read the entire item. Last updated 2. 2 May 2. Find the values of a table column c. To get an overview of the values of c. SELECT. GROUP_CONCAT(c. ORDER BY c. 2) AS 'C2 values'. To retrieve a list of c. IN clause specifying the c. HAVING clause specifying the required number of different items in the list .. SELECT c. 1. WHERE c. IN (1,2,3,4). HAVING COUNT(DISTINCT c. This is easy to generalise to multiple column expressions, and a HAVING clause specifying any number of items from the IN list. Last updated 2. 2 May 2. You have an election database with tables for candidates, parties and districts. A candidate belongs to one party; a district may have any number of candidates: DROP TABLE IF EXISTS parties,districts,candidates. CREATE TABLE parties (. NOT NULL. PRIMARY KEY (party). ENGINE=My. ISAM DEFAULT CHARSET=latin. INSERT INTO parties VALUES ('Conservative'),('Liberal'),('Socialist'),('Green'),('Libertarian'). CREATE TABLE districts (. DEFAULT NULL. ) ENGINE=My. ISAM DEFAULT CHARSET=latin. INSERT INTO districts VALUES ('Essex'),('Malton'),('Riverdale'),('Guelph'),('Halton'). Read the entire item. Last updated 1. 9 Mar 2. Given this table .. Read the entire item. Last updated 1. 9 Jan 2. In a good introductory tutorial on My. SQL subqueries, Jeremy Cole developed a triply nested query to retrieve the name, population, head of state, and number of official languages in countries with the most official languages. The query uses two tables from the My. SQL world database: CREATE TABLE country (. Code char(3) NOT NULL DEFAULT '' PRIMARY KEY. Name char(5. 2) NOT NULL DEFAULT ''. Population int(1. NOT NULL DEFAULT '0'. Head. Of. State char(6. DEFAULT NULL. .. other columns .. CREATE TABLE countrylanguage (. Country. Code char(3) NOT NULL DEFAULT '' PRIMARY KEY. Language char(3. 0) NOT NULL DEFAULT ''. Is. Official enum('T','F') NOT NULL DEFAULT 'F'. Read the entire item. Last updated 2. 2 May 2. To obtain part sums of a column based on specific conditions, use If() or CASE, eg .. Last updated 1. 6 Aug 2. When you have parent- child- grandchild tables, eg companies, users, actions, and your query requirement is for per- parent aggregates from the child table and per- child aggregates from the grandchild table, then cascading joins yield spuriously multiplied counts, and correlated subqueries fail because the second correlated subquery cannot find a visible joining column. One solution is to use derived tables. Assuming .. DROP TABLE IF EXISTS companies,users,actions. CREATE TABLE companies (id int, name char(1. INSERT INTO COMPANIES VALUES(1,'abc ltd'),(2,'xyz inc'). CREATE TABLE users (id INT,companyid INT). INSERT INTO users VALUES(1,1),(2,1),(3,1),(4,2),(5,2). CREATE TABLE actions (id INT, userid INT, date DATE). INSERT INTO actions VALUES. Read the entire item. Last updated 2. 2 May 2. Rather than list instances including repeats on one line, you want to list distinct instances and their counts. One way is to do a simple GROUP BY query and in your application layer remove the newlines from the result. Or you can do it in one step: drop table if exists t. Last updated 2. 8 Jun 2. If you need a child table aggregate result for each row in a query, what's faster, putting the correlated aggregating subquery in the SELECT list, or in the FROM clause? Thus, given tables stocks(id, user, ticker,lastvalue) and transactions(id, sid, action, qty, price, commission), and the requirement to retrieve stock values and per- stock transaction counts, which of these queries will run faster? N. .. or .. select s. N. select sid, count(*) N. The first query's syntax is simpler, so it's often the first choice. EXPLAIN reports that the second query requires examination of more rows than the first query does. But benchmarks with caching turned off show that the second query is much faster- -mainly because it executes one subquery rather than a subquery per row. If you have a choice, put the aggregating subquery in the FROM clause. Last updated 1. 0 Oct 2. Given the table authorbook(authid INT, bookid INT), what query finds the books who have authors with more than one book in the table? Even one level of recursion can induce a mild trance. Escape the trance by taking the problem one step at a time. First write the query that finds the authors with multiple books. Then join an outer query to that on authorid, and have the outer query select bookid: SELECT a. FROM authorbook a. SELECT authid,count(bookid). FROM authorbook a. GROUP BY authid. HAVING COUNT(bookid)> 1. AS a. 3 ON a. 1. authid=a. Last updated 2. 2 May 2. A superstore is running a promotion: each day, every fifth a customer wins a prize. Each day you're given a text file with data columns for customer. ID, timestamp and order amount. How do you find every fifth customer? Load the data into a table, then write the query inside out; with this kind of problem, nested queries are a boon. Using Load Data Infile, load the text file into a table (indexless for speed) named tbl with columns for customer. ID, timestamp and amount, and index the table on (customer. ID, timestamp). 2. Write a query that tracks per- customer order by timestamp with user variables: set @id='', @ord=1. Read the entire item. Last updated 1. 2 Sep 2. To group rows by a time period whose length in minutes divides evenly into 6. GROUP BY ((6. 0/period. Minutes) * HOUR( thistime ) + FLOOR( MINUTE( thistime ) / period. Minutes )). where thistime is the TIME column and period. Minutes is the period length in minutes. So to group by 1. SELECT .. GROUP BY ( 4 * HOUR( thistime ) + FLOOR( MINUTE( thistime ) / 1. Read the entire item. Last updated 2. 6 Aug 2. Here is a simple soccer league table setup that was developed in the My. SQL Forum by J Williams and a contributor named "Laptop Alias". The teams table tracks team ID and name, the games table tracks home and away team IDs and goal totals for each game. The query for standings is built by aggregating a UNION of home team and away team game results: DROP TABLE IF EXISTS teams, games. CREATE TABLE teams(id int primary key auto_increment,tname char(3. CREATE TABLE games(id int primary key auto_increment, date datetime.
0 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. Archives
October 2017
Categories |