sql_session_examples.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 106,
   "id": "7acac699-d6c7-4440-8740-4b2ab44e4881",
   "metadata": {},
   "outputs": [],
   "source": [
    "from proj_trial2 import *"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5436d46d-a10d-47be-8456-0b1dd8c530c7",
   "metadata": {},
   "source": [
    "# Practice Session Examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 110,
   "id": "56133be1-c5c7-46bb-94e6-df769510ac1a",
   "metadata": {
    "tags": []
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  ________            _____ ____    __ \n",
      " /_  __/ /_  ___     / ___// __ \\  / / \n",
      "  / / / __ \\/ _ \\    \\__ \\/ / / / / /  \n",
      " / / / / / /  __/   ___/ / /_/ / / /___\n",
      "/_/ /_/ /_/\\___/   /____/\\___\\_\\/_____/\n",
      "                                       \n",
      "    ____                  __  _              __  __      __  \n",
      "   / __ \\_________ ______/ /_(_)_______     / / / /_  __/ /_ \n",
      "  / /_/ / ___/ __ `/ ___/ __/ / ___/ _ \\   / /_/ / / / / __ \\\n",
      " / ____/ /  / /_/ / /__/ /_/ / /__/  __/  / __  / /_/ / /_/ /\n",
      "/_/   /_/   \\__,_/\\___/\\__/_/\\___/\\___/  /_/ /_/\\__,_/_.___/ \n",
      "                                                             \n",
      "\n",
      "Welcome, jqkcl, to your SQL Practice Hub! Here you'll be able to practice writing SQL queries in 6 different topics and get instant feedback.\n",
      "\n",
      "Choose a Topic.\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter in the corresponding number for the topic you want to study. For example, if you would like to practice SQL JOINS, enter 6.\n",
      "1 for Retrieve data from tables\n",
      "2 for Boolean and Relational Operators\n",
      "3 for Wildcard and Special operators\n",
      "4 for Aggregate Functions\n",
      "5 for Formatting query output\n",
      "6 for SQL JOINS\n",
      "Your choice:  2\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************************************\n",
      "\n",
      "Exercise:\n",
      "From the following table, write a SQL query to find all orders that meet the following conditions. Exclude combinations of order date equal to '2012-08-17' or customer ID greater than 3005 and purchase amount less than 1000. \n",
      "\n",
      "See sample table(s) below:\n",
      "Table name: orders\n",
      "\n",
      "ord_no      purch_amt   ord_date    customer_id  salesman_id\n",
      "----------  ----------  ----------  -----------  -----------\n",
      "70001       150.5       2012-10-05  3005         5002\n",
      "70009       270.65      2012-09-10  3001         5005\n",
      "70002       65.26       2012-10-05  3002         5001\n",
      "70004       110.5       2012-08-17  3009         5003\n",
      "70007       948.5       2012-09-10  3005         5002\n",
      "70005       2400.6      2012-07-27  3007         5001\n",
      "70008       5760        2012-09-10  3002         5001\n",
      "70010       1983.43     2012-10-10  3004         5006\n",
      "70003       2480.4      2012-10-10  3009         5003\n",
      "70012       250.45      2012-06-27  3008         5002\n",
      "70011       75.29       2012-08-17  3003         5007\n",
      "70013       3045.6      2012-04-25  3002         5001\n",
      "\n",
      "Please enter in your solution as one line (do not hit the Enter or Return key, and do not enter new lines)\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter your solution here: select * from orders where ord_date != '2012-08-13' or (customer_id > 3005 and purch_amt > 1000)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "************************************\n",
      "Feedback:\n",
      "The input SQL query is incorrect with fatal errors. The correct SQL query uses the NOT operator to invert the conditions in the WHERE clause. The input SQL query instead uses the != operator. The input SQL query also has the conditions in the WHERE clause in the wrong order. The correct order should have been (ord_date ='2012-08-17' OR customer_id>3005) AND purch_amt<1000.\n",
      "Suggested topics to study: logical operators, order of operations in SQL\n",
      "************************************\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "If you would like to try this question again, enter 1. If you would like to try a different question in this topic, enter 2. If you would like to try a new topic, enter 3. If you would like to exit your session, enter 4:  4\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  ________                __                        __\n",
      " /_  __/ /_  ____ _____  / /__   __  ______  __  __/ /\n",
      "  / / / __ \\/ __ `/ __ \\/ //_/  / / / / __ \\/ / / / / \n",
      " / / / / / / /_/ / / / / ,<    / /_/ / /_/ / /_/ /_/  \n",
      "/_/ /_/ /_/\\__,_/_/ /_/_/|_|   \\__, /\\____/\\__,_(_)   \n",
      "                              /____/                  \n",
      "\n",
      "We hope The SQL Practice Hub helped you learn SQL or prepare for your next interview :)\n"
     ]
    }
   ],
   "source": [
    "session = SQLSession('jqkcl')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 150,
   "id": "0e0f124f-9166-47cd-9324-edbf27a2c494",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  ________            _____ ____    __ \n",
      " /_  __/ /_  ___     / ___// __ \\  / / \n",
      "  / / / __ \\/ _ \\    \\__ \\/ / / / / /  \n",
      " / / / / / /  __/   ___/ / /_/ / / /___\n",
      "/_/ /_/ /_/\\___/   /____/\\___\\_\\/_____/\n",
      "                                       \n",
      "    ____                  __  _              __  __      __  \n",
      "   / __ \\_________ ______/ /_(_)_______     / / / /_  __/ /_ \n",
      "  / /_/ / ___/ __ `/ ___/ __/ / ___/ _ \\   / /_/ / / / / __ \\\n",
      " / ____/ /  / /_/ / /__/ /_/ / /__/  __/  / __  / /_/ / /_/ /\n",
      "/_/   /_/   \\__,_/\\___/\\__/_/\\___/\\___/  /_/ /_/\\__,_/_.___/ \n",
      "                                                             \n",
      "\n",
      "Welcome, jqkcl, to your SQL Practice Hub! Here you'll be able to practice writing SQL queries in 6 different topics and get instant feedback.\n",
      "\n",
      "Choose a Topic.\n",
      "\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter in the corresponding number for the topic you want to study. For example, if you would like to practice SQL JOINS, enter 6.\n",
      "1 for Retrieve data from tables\n",
      "2 for Boolean and Relational Operators\n",
      "3 for Wildcard and Special operators\n",
      "4 for Aggregate Functions\n",
      "5 for Formatting query output\n",
      "6 for SQL JOINS\n",
      "Your choice:  1\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************************************\n",
      "\n",
      "Exercise:\n",
      " From the following table, write a SQL query to combine the winners in 'Physiology' before 1971 and winners in 'Peace' on or after 1974. Return year, subject, winner, country, and category.\n",
      "\n",
      "See sample table(s) below:\n",
      "Table name: nobel_win\n",
      "\n",
      "\n",
      "YEAR SUBJECT                   WINNER                                        COUNTRY                CATEGORY\n",
      "---- ------------------------- --------------------------------------------- ------------------------- ------------\n",
      "1970 Physics                   Hannes Alfven                                 Sweden                 Scientist\n",
      "1970 Physics                   Louis Neel                                    France                 Scientist\n",
      "1970 Chemistry                 Luis Federico Leloir                          France                 Scientist\n",
      "1970 Physiology                Ulf von Euler                                 Sweden                 Scientist\n",
      "1970 Physiology                Bernard Katz                                  Germany                Scientist\n",
      "1970 Literature                Aleksandr Solzhenitsyn                        Russia                 Linguist\n",
      "1970 Economics                 Paul Samuelson                                USA                    Economist\n",
      "1970 Physiology                Julius Axelrod                                USA                    Scientist\n",
      "1971 Physics                   Dennis Gabor                                  Hungary                Scientist\n",
      "1971 Chemistry                 Gerhard Herzberg                              Germany                Scientist\n",
      "1971 Peace                     Willy Brandt                                  Germany                Chancellor\n",
      "1971 Literature                Pablo Neruda                                  Chile                  Linguist\n",
      "1971 Economics                 Simon Kuznets                                 Russia                 Economist\n",
      "1978 Peace                     Anwar al-Sadat                                Egypt                  President\n",
      "1978 Peace                     Menachem Begin                                Israel                 Prime Minister\n",
      "1987 Chemistry                 Donald J. Cram                                USA                    Scientist\n",
      "1987 Chemistry                 Jean-Marie Lehn                               France                 Scientist\n",
      "1987 Physiology                Susumu Tonegawa                               Japan                  Scientist\n",
      "1994 Economics                 Reinhard Selten                               Germany                Economist\n",
      "1994 Peace                     Yitzhak Rabin                                 Israel                 Prime Minister\n",
      "1987 Physics                   Johannes Georg Bednorz                        Germany                Scientist\n",
      "1987 Literature                Joseph Brodsky                                Russia                 Linguist\n",
      "1987 Economics                 Robert Solow                                  USA                    Economist\n",
      "1994 Literature                Kenzaburo Oe                                  Japan                  Linguist\n",
      "\n",
      "Please enter in your solution as one line (do not hit the Enter or Return key, and do not enter new lines)\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter your solution here: selet WINNER from nobel_win where (SUBJECT = 'Physiology' and YEAR < 1971) or (SUBJECT = 'Peace' and year >= 1974)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Topic: Retrieve data from tables #21\n",
      "************************************\n",
      "Example solution:\n",
      "SELECT * FROM nobel_win WHERE (subject ='Physiology' AND year<1971) UNION (SELECT * FROM nobel_win WHERE (subject ='Peace' AND year>=1974));\n",
      "\n",
      "************************************\n",
      "Feedback:\n",
      "The input SQL query is incorrect with fatal and minor errors. The correct SQL query uses the UNION operator to combine the results of two different SQL queries. The input SQL query instead uses the OR operator. The minor errors are with typos in the column names. The input SQL query uses \"WINNER\" and \"selet\" instead of \"winner\" and \"select\".\n",
      "Suggested topics to study: UNION operator, OR operator\n",
      "************************************\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "If you would like to try this question again, enter 1. If you would like to try a different question in this topic, enter 2. If you would like to try a new topic, enter 3. If you would like to exit your session, enter 4:  4\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  ________                __                        __\n",
      " /_  __/ /_  ____ _____  / /__   __  ______  __  __/ /\n",
      "  / / / __ \\/ __ `/ __ \\/ //_/  / / / / __ \\/ / / / / \n",
      " / / / / / / /_/ / / / / ,<    / /_/ / /_/ / /_/ /_/  \n",
      "/_/ /_/ /_/\\__,_/_/ /_/_/|_|   \\__, /\\____/\\__,_(_)   \n",
      "                              /____/                  \n",
      "\n",
      "We hope The SQL Practice Hub helped you learn SQL or prepare for your next interview :)\n"
     ]
    }
   ],
   "source": [
    "session = SQLSession('jqkcl')"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d77b5793-6e6e-4cc2-b551-77fa300f8167",
   "metadata": {},
   "source": [
    "# Interview Examples"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "f7c26388-c48a-4acd-949f-a9c3501e3607",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Welcome, jqkcl, to your SQL Interview Simulator. Here, you'll be asked 5 random SQL questions to assess your skills. At the end, you will be provided with feedback on each question. \n",
      "\n",
      "************************************\n",
      "\n",
      "Exercise:\n",
      "From the following tables write a SQL query to select all rows from both participating tables as long as there is a match between pro_com and com_id.\n",
      "\n",
      "See sample table(s) below:\n",
      "Table name: company_mast\n",
      "\n",
      "COM_ID COM_NAME\n",
      "------ -------------\n",
      "    11 Samsung\n",
      "    12 iBall\n",
      "    13 Epsion\n",
      "    14 Zebronics\n",
      "    15 Asus\n",
      "    16 Frontech\n",
      "\n",
      "Table name: item_mast\n",
      "\n",
      " PRO_ID PRO_NAME                       PRO_PRICE    PRO_COM\n",
      "------- ------------------------- -------------- ----------\n",
      "    101 Mother Board                    3200.00         15\n",
      "    102 Key Board                        450.00         16\n",
      "    103 ZIP drive                        250.00         14\n",
      "    104 Speaker                          550.00         16\n",
      "    105 Monitor                         5000.00         11\n",
      "    106 DVD drive                        900.00         12\n",
      "    107 CD drive                         800.00         12\n",
      "    108 Printer                         2600.00         13\n",
      "    109 Refill cartridge                 350.00         13\n",
      "    110 Mouse                            250.00         12\n",
      "\n",
      "Please enter in your solution as one line (do not hit the Enter or Return key, and do not enter new lines)\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter your solution here: select * from company_mast c inner join item_mast i on c.COM_ID = i.PRO_COM\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************************************\n",
      "\n",
      "Exercise:\n",
      "From the following table, write a SQL query to retrieve the details of the customers whose names begins with the letter 'B'. Return customer_id, cust_name, city, grade, salesman_id.\n",
      "\n",
      "See sample table(s) below:\n",
      "Table name: customer\n",
      "\n",
      " customer_id |   cust_name    |    city    | grade | salesman_id \n",
      "-------------+----------------+------------+-------+-------------\n",
      "        3002 | Nick Rimando   | New York   |   100 |        5001\n",
      "        3007 | Brad Davis     | New York   |   200 |        5001\n",
      "        3005 | Graham Zusi    | California |   200 |        5002\n",
      "        3008 | Julian Green   | London     |   300 |        5002\n",
      "        3004 | Fabian Johnson | Paris      |   300 |        5006\n",
      "        3009 | Geoff Cameron  | Berlin     |   100 |        5003\n",
      "        3003 | Jozy Altidor   | Moscow     |   200 |        5007\n",
      "        3001 | Brad Guzan     | London     |       |        5005\n",
      "\n",
      "Please enter in your solution as one line (do not hit the Enter or Return key, and do not enter new lines)\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter your solution here: select * from customer where cust_name like 'B%'\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Question 1 Results:\n",
      "Topic: SQL JOINS. Question #21\n",
      "************************************\n",
      "Example solution:\n",
      "SELECT *\n",
      "   FROM item_mast \n",
      "   INNER JOIN company_mast\n",
      "   ON item_mast.pro_com= company_mast.com_id; \n",
      "\n",
      "************************************\n",
      "Feedback:\n",
      "The input SQL query is correct. It would produce the same output as the solution SQL query. The only difference is the order of the tables. The solution SQL query has the item_mast table first and then the company_mast table. The input SQL query has the company_mast table first and then the item_mast table. The order of the tables does not matter in SQL.\n",
      "Suggested topics to study: types of joins\n",
      "Question 2 Results:\n",
      "Topic: Wildcard and Special operators. Question #9\n",
      "************************************\n",
      "Example solution:\n",
      "SELECT *\n",
      "FROM customer\n",
      "WHERE cust_name LIKE 'B%';\n",
      "\n",
      "************************************\n",
      "Feedback:\n",
      "The input SQL query is correct. It would produce the same output as the solution SQL query. The only difference is that the solution SQL query has extra spaces which are not necessary.\n",
      "  ________                __                        __\n",
      " /_  __/ /_  ____ _____  / /__   __  ______  __  __/ /\n",
      "  / / / __ \\/ __ `/ __ \\/ //_/  / / / / __ \\/ / / / / \n",
      " / / / / / / /_/ / / / / ,<    / /_/ / /_/ / /_/ /_/  \n",
      "/_/ /_/ /_/\\__,_/_/ /_/_/|_|   \\__, /\\____/\\__,_(_)   \n",
      "                              /____/                  \n",
      "\n",
      "We hope The SQL Interview Simulator helped you get one step closer to getting your dream job! Good luck :)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<proj_trial2.SQLInterview at 0x7f787221f160>"
      ]
     },
     "execution_count": 152,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SQLInterview('jqkcl', 2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 149,
   "id": "2df77f61-2da1-4008-8b68-abcb3274ca76",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Welcome, jqkcl, to your SQL Interview Simulator. Here, you'll be asked 5 random SQL questions to assess your skills. At the end, you will be provided with feedback on each question. \n",
      "\n",
      "************************************\n",
      "\n",
      "Exercise:\n",
      "From the following tables write a SQL query to locate those salespeople who do not live in the same city where their customers live and have received a commission of more than 12% from the company. Return Customer Name, customer city, Salesman, salesman city, commission.\n",
      "\n",
      "See sample table(s) below:\n",
      "Table name: customer\n",
      "\n",
      " customer_id |   cust_name    |    city    | grade | salesman_id \n",
      "-------------+----------------+------------+-------+-------------\n",
      "        3002 | Nick Rimando   | New York   |   100 |        5001\n",
      "        3007 | Brad Davis     | New York   |   200 |        5001\n",
      "        3005 | Graham Zusi    | California |   200 |        5002\n",
      "        3008 | Julian Green   | London     |   300 |        5002\n",
      "        3004 | Fabian Johnson | Paris      |   300 |        5006\n",
      "        3009 | Geoff Cameron  | Berlin     |   100 |        5003\n",
      "        3003 | Jozy Altidor   | Moscow     |   200 |        5007\n",
      "        3001 | Brad Guzan     | London     |       |        5005\n",
      "\n",
      "Table name: salesman\n",
      "\n",
      " salesman_id |    name    |   city   | commission \n",
      "-------------+------------+----------+------------\n",
      "        5001 | James Hoog | New York |       0.15\n",
      "        5002 | Nail Knite | Paris    |       0.13\n",
      "        5005 | Pit Alex   | London   |       0.11\n",
      "        5006 | Mc Lyon    | Paris    |       0.14\n",
      "        5007 | Paul Adam  | Rome     |       0.13\n",
      "        5003 | Lauson Hen | San Jose |       0.12\n",
      "\n",
      "Please enter in your solution as one line (do not hit the Enter or Return key, and do not enter new lines)\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter your solution here: select c.cust_name, c.city, c.salesman_id, s.city, s.commission from customer c, salesman s, where c.salesman_id = s.salesman_id and c.city != s.city and s.commission > .12\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************************************\n",
      "\n",
      "Exercise:\n",
      "From the following table, write a SQL query to find all the customers in ‘New York’ city who have a grade value above 100. Return customer_id, cust_name, city, grade, and salesman_id.\n",
      "\n",
      "See sample table(s) below:\n",
      "Table name: customer\n",
      "\n",
      " customer_id |   cust_name    |    city    | grade | salesman_id \n",
      "-------------+----------------+------------+-------+-------------\n",
      "        3002 | Nick Rimando   | New York   |   100 |        5001\n",
      "        3007 | Brad Davis     | New York   |   200 |        5001\n",
      "        3005 | Graham Zusi    | California |   200 |        5002\n",
      "        3008 | Julian Green   | London     |   300 |        5002\n",
      "        3004 | Fabian Johnson | Paris      |   300 |        5006\n",
      "        3009 | Geoff Cameron  | Berlin     |   100 |        5003\n",
      "        3003 | Jozy Altidor   | Moscow     |   200 |        5007\n",
      "        3001 | Brad Guzan     | London     |       |        5005\n",
      "\n",
      "Please enter in your solution as one line (do not hit the Enter or Return key, and do not enter new lines)\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter your solution here: select * from customer where city = 'New York' and grade > 100\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************************************\n",
      "\n",
      "Exercise:\n",
      "From the following table, write a SQL query to determine the maximum order (purchase) amount generated by each salesperson. Filter the rows for the salesperson ID is in the range 5003 and 5008 (Begin and end values are included.). Return salesperson id and maximum purchase amount.\n",
      "\n",
      "See sample table(s) below:\n",
      "Table name: orders\n",
      "\n",
      "ord_no      purch_amt   ord_date    customer_id  salesman_id\n",
      "----------  ----------  ----------  -----------  -----------\n",
      "70001       150.5       2012-10-05  3005         5002\n",
      "70009       270.65      2012-09-10  3001         5005\n",
      "70002       65.26       2012-10-05  3002         5001\n",
      "70004       110.5       2012-08-17  3009         5003\n",
      "70007       948.5       2012-09-10  3005         5002\n",
      "70005       2400.6      2012-07-27  3007         5001\n",
      "70008       5760        2012-09-10  3002         5001\n",
      "70010       1983.43     2012-10-10  3004         5006\n",
      "70003       2480.4      2012-10-10  3009         5003\n",
      "70012       250.45      2012-06-27  3008         5002\n",
      "70011       75.29       2012-08-17  3003         5007\n",
      "70013       3045.6      2012-04-25  3002         5001\n",
      "\n",
      "Please enter in your solution as one line (do not hit the Enter or Return key, and do not enter new lines)\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter your solution here: select salesman_id, max(purch_amt) from orders where salesman_id in (5003, 5004, 5005, 5006, 5007, 5008) group by salesman_id\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************************************\n",
      "\n",
      "Exercise:\n",
      "From the following table, write a SQL query to select all the salespeople. Return salesman_id, name, city, commission with the percent sign (%).\n",
      "\n",
      "See sample table(s) below:\n",
      "Table name: salesman\n",
      "\n",
      " salesman_id |    name    |   city   | commission \n",
      "-------------+------------+----------+------------\n",
      "        5001 | James Hoog | New York |       0.15\n",
      "        5002 | Nail Knite | Paris    |       0.13\n",
      "        5005 | Pit Alex   | London   |       0.11\n",
      "        5006 | Mc Lyon    | Paris    |       0.14\n",
      "        5007 | Paul Adam  | Rome     |       0.13\n",
      "        5003 | Lauson Hen | San Jose |       0.12\n",
      "\n",
      "Please enter in your solution as one line (do not hit the Enter or Return key, and do not enter new lines)\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter your solution here: select salesman_id, name, city, concat(commission * 100, '%') from salesman\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "************************************\n",
      "\n",
      "Exercise:\n",
      "From the following table, write a SQL query to find the details of the customers whose names end with the letter 'n'. Return customer_id, cust_name, city, grade, salesman_id.\n",
      "\n",
      "See sample table(s) below:\n",
      "Table name: customer\n",
      "\n",
      " customer_id |   cust_name    |    city    | grade | salesman_id \n",
      "-------------+----------------+------------+-------+-------------\n",
      "        3002 | Nick Rimando   | New York   |   100 |        5001\n",
      "        3007 | Brad Davis     | New York   |   200 |        5001\n",
      "        3005 | Graham Zusi    | California |   200 |        5002\n",
      "        3008 | Julian Green   | London     |   300 |        5002\n",
      "        3004 | Fabian Johnson | Paris      |   300 |        5006\n",
      "        3009 | Geoff Cameron  | Berlin     |   100 |        5003\n",
      "        3003 | Jozy Altidor   | Moscow     |   200 |        5007\n",
      "        3001 | Brad Guzan     | London     |       |        5005\n",
      "\n",
      "Please enter in your solution as one line (do not hit the Enter or Return key, and do not enter new lines)\n"
     ]
    },
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      "Enter your solution here: select * from customer where cust_name like '%n'\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Question 1 Results:\n",
      "************************************\n",
      "Feedback:\n",
      "The input SQL query is incorrect with fatal and minor errors. The correct SQL query uses an inner join while the input SQL query uses a regular join. An inner join only returns rows where there is a match in both tables while a regular join will return all rows from both tables, even if there is no match. In this case, both queries would produce the same output because all the rows in both tables match on the city column. The minor errors are with the column and table aliases. The solution SQL query uses the aliases \"a\" for the customer table and \"b\" for the salesman table. The input SQL query does not use any aliases. The solution SQL query also uses the column alias \"Customer Name\" for the customer name column. The input SQL query does not use any column aliases.\n",
      "Suggested topics to study: types of joins, table and column aliases\n",
      "Question 2 Results:\n",
      "************************************\n",
      "Feedback:\n",
      "The input SQL query is correct. It would produce the same output as the solution SQL query. The only difference is that the input SQL query does not have spaces between each line while the solution SQL query does. This does not affect the output of the query.\n",
      "Suggested topics to study: none\n",
      "Question 3 Results:\n",
      "************************************\n",
      "Feedback:\n",
      "The input SQL query is incorrect with fatal and minor errors. The solution SQL query uses the HAVING clause to filter the rows after the GROUP BY is applied. The input SQL query instead uses the WHERE clause to filter the rows before the GROUP BY is applied. This would produce different results. The minor error is that the input SQL query is missing a space between the max function and the open parenthesis.\n",
      "Suggested topics to study: GROUP BY, HAVING, and ORDER BY clauses\n",
      "Question 4 Results:\n",
      "************************************\n",
      "Feedback:\n",
      "The input SQL query is correct. It would produce the same output as the solution SQL query. The only difference is that the input SQL query uses the CONCAT function while the solution SQL query uses the '%' wildcard. The CONCAT function concatenates two strings together while the '%' wildcard just adds the '%' character to the end of the commission value. In this case, both queries would produce the same output because the end result is the same. \n",
      "Suggested topics to study: string functions\n",
      "Question 5 Results:\n",
      "************************************\n",
      "Feedback:\n",
      "The input SQL query is correct. It would produce the same output as the solution SQL query. The only difference is that the solution SQL query has extra spaces which are not necessary.\n",
      "  ________                __                        __\n",
      " /_  __/ /_  ____ _____  / /__   __  ______  __  __/ /\n",
      "  / / / __ \\/ __ `/ __ \\/ //_/  / / / / __ \\/ / / / / \n",
      " / / / / / / /_/ / / / / ,<    / /_/ / /_/ / /_/ /_/  \n",
      "/_/ /_/ /_/\\__,_/_/ /_/_/|_|   \\__, /\\____/\\__,_(_)   \n",
      "                              /____/                  \n",
      "\n",
      "We hope The SQL Interview Simulator helped you get one step closer to getting your dream job! Good luck :)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "<proj_trial2.SQLInterview at 0x7f78000da5e0>"
      ]
     },
     "execution_count": 149,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "SQLInterview('jqkcl')"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}