Database: cre_Doc_Template_Mgt
Schema: CREATE TABLE Ref_Template_Types ( Template_Type_Code CHAR(15) NOT NULL, Template_Type_Description VARCHAR(255) NOT NULL, PRIMARY KEY (Template_Type_Code) ); CREATE TABLE Templates ( Template_ID INTEGER NOT NULL, Version_Number INTEGER NOT NULL, Template_Type_Code CHAR(15) NOT NULL, Date_Effective_From DATETIME, Date_Effective_To DATETIME, Template_Details VARCHAR(255) NOT NULL, PRIMARY KEY (Template_ID), FOREIGN KEY (Template_Type_Code) REFERENCES Ref_Template_Types (Template_Type_Code) ); CREATE TABLE Documents ( Document_ID INTEGER NOT NULL, Template_ID INTEGER, Document_Name VARCHAR(255), Document_Description VARCHAR(255), Other_Details VARCHAR(255), PRIMARY KEY (Document_ID), FOREIGN KEY (Template_ID) REFERENCES Templates (Template_ID) ); CREATE TABLE Paragraphs ( Paragraph_ID INTEGER NOT NULL, Document_ID INTEGER NOT NULL, Paragraph_Text VARCHAR(255), Other_Details VARCHAR(255), PRIMARY KEY (Paragraph_ID), FOREIGN KEY (Document_ID) REFERENCES Documents (Document_ID) );

Question (easy): How many documents do we have?
SQL: SELECT count(*) FROM Documents

Question (easy): Count the number of documents.
SQL: SELECT count(*) FROM Documents

Question (medium): List document IDs, document names, and document descriptions for all documents.
SQL: SELECT document_id ,  document_name ,  document_description FROM Documents

Question (medium): What are the ids, names, and descriptions for all documents?
SQL: SELECT document_id ,  document_name ,  document_description FROM Documents

Question (medium): What is the document name and template id for document with description with the letter 'w' in it?
SQL: SELECT document_name ,  template_id FROM Documents WHERE Document_Description LIKE "%w%"

Question (medium): Return the names and template ids for documents that contain the letter w in their description.
SQL: SELECT document_name ,  template_id FROM Documents WHERE Document_Description LIKE "%w%"

Question (medium): What is the document id, template id and description for document named "Robbin CV"?
SQL: SELECT document_id ,  template_id ,  Document_Description FROM Documents WHERE document_name  =  "Robbin CV"

Question (medium): Return the document id, template id, and description for the document with the name Robbin CV.
SQL: SELECT document_id ,  template_id ,  Document_Description FROM Documents WHERE document_name  =  "Robbin CV"

Question (easy): How many different templates do all document use?
SQL: SELECT count(DISTINCT template_id) FROM Documents

Question (easy): Count the number of different templates used for documents.
SQL: SELECT count(DISTINCT template_id) FROM Documents

Question (medium): How many documents are using the template with type code 'PPT'?
SQL: SELECT count(*) FROM Documents AS T1 JOIN Templates AS T2 ON T1.Template_ID  =  T2.Template_ID WHERE T2.Template_Type_Code  =  'PPT'

Question (medium): Count the number of documents that use the PPT template type.
SQL: SELECT count(*) FROM Documents AS T1 JOIN Templates AS T2 ON T1.Template_ID  =  T2.Template_ID WHERE T2.Template_Type_Code  =  'PPT'

Question (medium): Show all template ids and number of documents using each template.
SQL: SELECT template_id ,  count(*) FROM Documents GROUP BY template_id

Question (medium): What are all different template ids used for documents, and how many times were each of them used?
SQL: SELECT template_id ,  count(*) FROM Documents GROUP BY template_id

Question (extra): What is the id and type code for the template used by the most documents?
SQL: SELECT T1.template_id ,  T2.Template_Type_Code FROM Documents AS T1 JOIN Templates AS T2 ON T1.template_id  =  T2.template_id GROUP BY T1.template_id ORDER BY count(*) DESC LIMIT 1

Question (extra): Return the id and type code of the template that is used for the greatest number of documents.
SQL: SELECT T1.template_id ,  T2.Template_Type_Code FROM Documents AS T1 JOIN Templates AS T2 ON T1.template_id  =  T2.template_id GROUP BY T1.template_id ORDER BY count(*) DESC LIMIT 1

Question (easy): Show ids for all templates that are used by more than one document.
SQL: SELECT template_id FROM Documents GROUP BY template_id HAVING count(*)  >  1

Question (easy): What are the template ids of any templates used in more than a single document?
SQL: SELECT template_id FROM Documents GROUP BY template_id HAVING count(*)  >  1

Question (hard): Show ids for all templates not used by any document.
SQL: SELECT template_id FROM Templates EXCEPT SELECT template_id FROM Documents

Question (hard): What are the ids for templates that are not used in any documents?
SQL: SELECT template_id FROM Templates EXCEPT SELECT template_id FROM Documents

Question (easy): How many templates do we have?
SQL: SELECT count(*) FROM Templates

Question (easy): Count the number of templates.
SQL: SELECT count(*) FROM Templates

Question (medium): Show template ids, version numbers, and template type codes for all templates.
SQL: SELECT template_id ,  version_number ,  template_type_code FROM Templates

Question (medium): What are the ids, version numbers, and type codes for each template?
SQL: SELECT template_id ,  version_number ,  template_type_code FROM Templates

Question (easy): Show all distinct template type codes for all templates.
SQL: SELECT DISTINCT template_type_code FROM Templates

Question (easy): What are the different template type codes?
SQL: SELECT DISTINCT template_type_code FROM Templates

Question (medium): What are the ids of templates with template type code PP or PPT?
SQL: SELECT template_id FROM Templates WHERE template_type_code  =  "PP" OR template_type_code  =  "PPT"

Question (medium): Return the ids of templates that have the code PP or PPT.
SQL: SELECT template_id FROM Templates WHERE template_type_code  =  "PP" OR template_type_code  =  "PPT"

Question (easy): How many templates have template type code CV?
SQL: SELECT count(*) FROM Templates WHERE template_type_code  =  "CV"

Question (easy): Count the number of templates of the type CV.
SQL: SELECT count(*) FROM Templates WHERE template_type_code  =  "CV"

Question (medium): What is the version number and template type code for the template with version number later than 5?
SQL: SELECT version_number ,  template_type_code FROM Templates WHERE version_number  >  5

Question (medium): Return the version numbers and template type codes of templates with a version number greater than 5.
SQL: SELECT version_number ,  template_type_code FROM Templates WHERE version_number  >  5

Question (medium): Show all template type codes and number of templates for each.
SQL: SELECT template_type_code ,  count(*) FROM Templates GROUP BY template_type_code

Question (medium): What are the different template type codes, and how many templates correspond to each?
SQL: SELECT template_type_code ,  count(*) FROM Templates GROUP BY template_type_code

Question (hard): Which template type code has most number of templates?
SQL: SELECT template_type_code FROM Templates GROUP BY template_type_code ORDER BY count(*) DESC LIMIT 1

Question (hard): Return the type code of the template type that the most templates belong to.
SQL: SELECT template_type_code FROM Templates GROUP BY template_type_code ORDER BY count(*) DESC LIMIT 1

Question (easy): Show all template type codes with less than three templates.
SQL: SELECT template_type_code FROM Templates GROUP BY template_type_code HAVING count(*)  <  3

Question (easy): What are the codes of template types that have fewer than 3 templates?
SQL: SELECT template_type_code FROM Templates GROUP BY template_type_code HAVING count(*)  <  3

Question (medium): What the smallest version number and its template type code?
SQL: SELECT min(Version_Number) ,  template_type_code FROM Templates

Question (medium): Return the lowest version number, along with its corresponding template type code.
SQL: SELECT min(Version_Number) ,  template_type_code FROM Templates

Question (medium): What is the template type code of the template used by document with the name "Data base"?
SQL: SELECT T1.template_type_code FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id WHERE T2.document_name  =  "Data base"

Question (medium): Return the template type code of the template that is used by a document named Data base.
SQL: SELECT T1.template_type_code FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id WHERE T2.document_name  =  "Data base"

Question (medium): Show all document names using templates with template type code BK.
SQL: SELECT T2.document_name FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id WHERE T1.template_type_code  =  "BK"

Question (medium): What are the names of documents that use templates with the code BK?
SQL: SELECT T2.document_name FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id WHERE T1.template_type_code  =  "BK"

Question (medium): Show all template type codes and the number of documents using each type.
SQL: SELECT T1.template_type_code ,  count(*) FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id GROUP BY T1.template_type_code

Question (medium): What are the different template type codes, and how many documents use each type?
SQL: SELECT T1.template_type_code ,  count(*) FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id GROUP BY T1.template_type_code

Question (extra): Which template type code is used by most number of documents?
SQL: SELECT T1.template_type_code FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id GROUP BY T1.template_type_code ORDER BY count(*) DESC LIMIT 1

Question (extra): Return the code of the template type that is most commonly used in documents.
SQL: SELECT T1.template_type_code FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id GROUP BY T1.template_type_code ORDER BY count(*) DESC LIMIT 1

Question (hard): Show all template type codes that are not used by any document.
SQL: SELECT template_type_code FROM Templates EXCEPT SELECT template_type_code FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id

Question (hard): What are the codes of template types that are not used for any document?
SQL: SELECT template_type_code FROM Templates EXCEPT SELECT template_type_code FROM Templates AS T1 JOIN Documents AS T2 ON T1.template_id  =  T2.template_id

Question (medium): Show all template type codes and descriptions.
SQL: SELECT template_type_code ,  template_type_description FROM Ref_template_types

Question (medium): What are the type codes and descriptions for all template types?
SQL: SELECT template_type_code ,  template_type_description FROM Ref_template_types

Question (easy): What is the template type descriptions for template type code "AD".
SQL: SELECT template_type_description FROM Ref_template_types WHERE template_type_code  =  "AD"

Question (easy): Return the template type description of the template type with the code AD.
SQL: SELECT template_type_description FROM Ref_template_types WHERE template_type_code  =  "AD"

Question (easy): What is the template type code for template type description "Book".
SQL: SELECT template_type_code FROM Ref_template_types WHERE template_type_description  =  "Book"

Question (easy): Return the type code of the template type with the description "Book".
SQL: SELECT template_type_code FROM Ref_template_types WHERE template_type_description  =  "Book"

Question (medium): What are the distinct template type descriptions for the templates ever used by any document?
SQL: SELECT DISTINCT T1.template_type_description FROM Ref_template_types AS T1 JOIN Templates AS T2 ON T1.template_type_code  = T2.template_type_code JOIN Documents AS T3 ON T2.Template_ID  =  T3.template_ID

Question (medium): Return the different descriptions for templates that have been used in a document.
SQL: SELECT DISTINCT T1.template_type_description FROM Ref_template_types AS T1 JOIN Templates AS T2 ON T1.template_type_code  = T2.template_type_code JOIN Documents AS T3 ON T2.Template_ID  =  T3.template_ID

Question (medium): What are the template ids with template type description "Presentation".
SQL: SELECT T2.template_id FROM Ref_template_types AS T1 JOIN Templates AS T2 ON T1.template_type_code  = T2.template_type_code WHERE T1.template_type_description  =  "Presentation"

Question (medium): Return the ids corresponding to templates with the description 'Presentation'.
SQL: SELECT T2.template_id FROM Ref_template_types AS T1 JOIN Templates AS T2 ON T1.template_type_code  = T2.template_type_code WHERE T1.template_type_description  =  "Presentation"

Question (easy): How many paragraphs in total?
SQL: SELECT count(*) FROM Paragraphs

Question (easy): Count the number of paragraphs.
SQL: SELECT count(*) FROM Paragraphs

Question (medium): How many paragraphs for the document with name 'Summer Show'?
SQL: SELECT count(*) FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_ID  =  T2.document_ID WHERE T2.document_name  =  'Summer Show'

Question (medium): Count the number of paragraphs in the document named 'Summer Show'.
SQL: SELECT count(*) FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_ID  =  T2.document_ID WHERE T2.document_name  =  'Summer Show'

Question (medium): Show paragraph details for paragraph with text 'Korea ' .
SQL: select other_details from paragraphs where paragraph_text like 'korea'

Question (medium): What are the details for the paragraph that includes the text 'Korea ' ?
SQL: select other_details from paragraphs where paragraph_text like 'korea'

Question (medium): Show all paragraph ids and texts for the document with name 'Welcome to NY'.
SQL: SELECT T1.paragraph_id ,   T1.paragraph_text FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id WHERE T2.Document_Name  =  'Welcome to NY'

Question (medium): What are the ids and texts of paragraphs in the document titled 'Welcome to NY'?
SQL: SELECT T1.paragraph_id ,   T1.paragraph_text FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id WHERE T2.Document_Name  =  'Welcome to NY'

Question (medium): Show all paragraph texts for the document "Customer reviews".
SQL: SELECT T1.paragraph_text FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id WHERE T2.document_name  =  "Customer reviews"

Question (medium): What are the paragraph texts for the document with the name 'Customer reviews'?
SQL: SELECT T1.paragraph_text FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id WHERE T2.document_name  =  "Customer reviews"

Question (medium): Show all document ids and the number of paragraphs in each document. Order by document id.
SQL: SELECT document_id ,  count(*) FROM Paragraphs GROUP BY document_id ORDER BY document_id

Question (medium): Return the different document ids along with the number of paragraphs corresponding to each, ordered by id.
SQL: SELECT document_id ,  count(*) FROM Paragraphs GROUP BY document_id ORDER BY document_id

Question (medium): Show all document ids, names and the number of paragraphs in each document.
SQL: SELECT T1.document_id ,  T2.document_name ,  count(*) FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id GROUP BY T1.document_id

Question (medium): What are the ids and names of each document, as well as the number of paragraphs in each?
SQL: SELECT T1.document_id ,  T2.document_name ,  count(*) FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id GROUP BY T1.document_id

Question (easy): List all document ids with at least two paragraphs.
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id HAVING count(*)  >=  2

Question (easy): What are the ids of documents that have 2 or more paragraphs?
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id HAVING count(*)  >=  2

Question (extra): What is the document id and name with greatest number of paragraphs?
SQL: SELECT T1.document_id ,  T2.document_name FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id GROUP BY T1.document_id ORDER BY count(*) DESC LIMIT 1

Question (extra): Return the id and name of the document with the most paragraphs.
SQL: SELECT T1.document_id ,  T2.document_name FROM Paragraphs AS T1 JOIN Documents AS T2 ON T1.document_id  =  T2.document_id GROUP BY T1.document_id ORDER BY count(*) DESC LIMIT 1

Question (hard): What is the document id with least number of paragraphs?
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id ORDER BY count(*) ASC LIMIT 1

Question (hard): Return the id of the document with the fewest paragraphs.
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id ORDER BY count(*) ASC LIMIT 1

Question (easy): What is the document id with 1 to 2 paragraphs?
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id HAVING count(*) BETWEEN 1 AND 2

Question (easy): Give the ids of documents that have between one and two paragraphs.
SQL: SELECT document_id FROM Paragraphs GROUP BY document_id HAVING count(*) BETWEEN 1 AND 2

Question (hard): Show the document id with paragraph text 'Brazil' and 'Ireland'.
SQL: SELECT document_id FROM Paragraphs WHERE paragraph_text  =  'Brazil' INTERSECT SELECT document_id FROM Paragraphs WHERE paragraph_text  =  'Ireland'

Question (hard): What are the ids of documents that contain the paragraph text 'Brazil' and 'Ireland'?
SQL: SELECT document_id FROM Paragraphs WHERE paragraph_text  =  'Brazil' INTERSECT SELECT document_id FROM Paragraphs WHERE paragraph_text  =  'Ireland'