applied ai sql assignment

Home - dataenthusiast092/Applied-AI-Assignments-Case-Studies GitHub Wiki

As a part of completing the coursework prescribed by the Applied-AI-Course , I have compiled all the latest work submitted by me on the Baymax classroom.

Optional Assignment

Initially 2 optional assignments were done:

• Python Practice Questions
• Pandas Practice Questions

Additionally, a mandatory SQL assignment was done.

Data Visualization

In the beginning of the course, Exploratory Data Analysis and Dimensionality Reduction assignments were done.

Supervised Learning

Later on, when diving into the course total 7 Supervised Machine Learning assignments were done:

• k-Nearest Neighbour
• Naive Bayes
• Logistic Regression
• SGD for Linear Regression
• Support Vector Machines
• Decision Trees
• Random Forest- Gradient Boosted Decision Trees

Unsupervised Learning

Also, Unsupervised Machine Learning assignments were done:

• k-Means, Agglomerative Hierarchical and DBSCAN Clustering
• Truncated Singular Value Decomposition

Deep Learning

Later on Deep Learning was done:

• Keras and Tensorflow

Supervised Learning Case Studies

Some Supervised Machine Learning Case Studies were also done:

• Quora Question pair Similarity
• Personalized Cancer Diagnosis
• Facebook Friend Recommendation

Work under construction..

Learn How to Use AI for SQL

Learn to generate SQL with AI, transform natural language to SQL, and utilize LLMs for SQL operations in our innovative course.

• AI assistance for guided coding help
• Projects to apply new skills
• Quizzes to test your knowledge
• A certificate of completion

Skill level

Time to complete

Prerequisites

• Intro to SQL
• Learn SQL: Aggregate Functions
• Learn SQL: Multiple Tables

About this course

Discover how to enhance your database analytics using AI technology. This course will guide you on how to employ AI systems like ChatGPT or Gemini for everything from brainstorming analytics ideas to writing and analyzing SQL queries. Along the way, we’ll dive into the ethics of using AI as well as potential pitfalls such as hallucinations, data security, and legal questions. Harness the combined power of human expertise and artificial intelligence to unlock new insights from your data.

Skills you'll gain

Generate SQL with AI

Use AI to debug and explain complex queries

Navigate potential pitfalls in working with AI for SQL

Learn AI for SQL

Learn how to use AI for SQL analytics.

Certificate of completion available with Plus or Pro

The platform

Hands-on learning

Projects in this course

Analyze ai systems with sql.

Earn a certificate of completion

• Show proof Receive a certificate that demonstrates you've completed a course or path.
• Build a collection The more courses and paths you complete, the more certificates you collect.
• Share with your network Easily add certificates of completion to your LinkedIn profile to share your accomplishments.

Reviews from learners

Our learners work at.

• Google Logo
• Amazon Logo
• Microsoft Logo
• Reddit Logo
• Spotify Logo
• YouTube Logo
• Instagram Logo

Frequently asked questions about How to Use AI for SQL

What is sql.

SQL (Structured Query Language) is a programming language used to manage data stored in relational databases, which store structured data in tables. Its syntax is easy to read, so it’s easy to pick up on even if you’re completely new to programming, and it’s even useful for non-technical careers.

What does SQL do?

What kind of jobs use sql, why should i use ai to help me write sql, join over 50 million learners and start learn how to use ai for sql today, looking for something else, related resources, design a custom game with generative ai, ai prompting best practices, detecting hallucinations in generative ai, related courses and paths, intro to ai strategy, learn how to use ai for data analysis, intro to generative ai, browse more topics.

• Data Analytics 2,282,219 learners enrolled
• Data Science 4,284,162 learners enrolled
• SQL 1,227,552 learners enrolled
• Code Foundations 7,128,800 learners enrolled
• Computer Science 5,578,802 learners enrolled
• Web Development 4,765,984 learners enrolled
• Python 3,461,952 learners enrolled
• For Business 3,152,657 learners enrolled
• JavaScript 2,774,700 learners enrolled

Unlock additional features with a paid plan

Practice projects, assessments, certificate of completion.

SQL Generators

7 best ai-powered sql generators in 2024.

By  Alex  • Updated Nov 1, 2023

SQL is key for gaining valuable insights from data. However, writing SQL queries is time-consuming and error-prone.

Luckily, AI is here to help. In this article, we’ll explore the 7 best AI SQL generators. These tools can write efficient, error-free SQL queries quickly, saving you time and effort. Let’s take a closer look!

Best AI SQL Generators

• Text2SQL.AI

What Makes a Great AI SQL Generator?

Before we delve into the list of the best AI SQL generators, let's discuss the essential qualities that make a great AI SQL generator:

• Database Compatibility : The AI SQL Generator should work with a variety of databases including MySQL, PostgreSQL, Snowflake, BigQuery, MS SQL Server, MariaDB, SQLite, and others.
• Query Optimization : The tool should optimize queries for better performance by analyzing the data and suggesting changes to the query structure or indexing, resulting in faster execution times and efficient resource usage.
• Error Detection and Auto-Completion : These features help suggest completions for queries and detect syntax errors, providing correction suggestions.
• Handling Complex Queries : The AI SQL generator should handle complex SQL queries, enabling users to perform advanced data analysis tasks effortlessly.
• Privacy and Security : The tool ensures strict privacy and security standards to safeguard your data.

Now that we know what to look for in an AI SQL generator, let's deep dive into our top picks.

1. BlazeSQL

BlazeSQL is a powerful AI SQL generator that enables users to write complex SQL queries with ease. It harnesses the power of AI to understand your database schema and generates optimized SQL queries based on your requirements.

Key Features:

• Natural Language Processing : BlazeSQL uses NLP to understand your query requirements and generate SQL queries accordingly.
• Database Documentation : BlazeSQL allows you to add information about your database columns, aiding in the generation of more accurate queries.
• Privacy and Security : BlazeSQL provides a desktop version that allows you to run queries and visualize your data locally, ensuring privacy and security.

Our Take: BlazeSQL is an excellent choice for those looking to save time on SQL query writing. Its ability to generate complex SQL queries based on simple English inputs makes it extremely user-friendly. However, its standout feature is its focus on data privacy and security, making it an excellent choice for businesses handling sensitive data.

2. Text2SQL.AI

Text2SQL.AI is an innovative AI SQL generator that can transform your thoughts into complex SQL queries. This tool uses the OpenAI GPT-3 Codex model to translate English prompts into SQL queries and vice versa.

• Support for Various SQL Operations : Text2SQL.AI supports a wide range of SQL operations including SELECT, UPDATE, DELETE queries, CREATE and ALTER TABLE requests, constraints, window functions, and more.
• Custom Database Schema Connection : You can connect your custom database schema, allowing Text2SQL.AI to generate more accurate queries.
• SQL Query Explanation : Text2SQL.AI can explain generated SQL queries in plain English, helping users understand the logic behind the queries.

Our Take: Text2SQL.AI is a robust AI SQL generator that can handle a wide range of SQL operations. Its ability to explain SQL queries in plain English sets it apart, making it an excellent learning tool for beginners and a powerful query generator for professionals.

AI2sql is an easy-to-use AI SQL generator that allows both engineers and non-engineers to write efficient, error-free SQL queries. All you need to do is enter a few keywords about your data, and AI2sql takes care of the rest.

• Easy Query Generation : AI2sql allows you to generate SQL queries by simply entering a few keywords about your data.
• Support for Multiple Databases : AI2sql supports various databases, making it a versatile tool for different database environments.
• Fast Performance : AI2sql generates optimized SQL queries for your data, resulting in extremely fast performance.

Our Take: AI2sql is a user-friendly tool that simplifies the process of writing SQL queries. Its keyword-based approach makes it accessible to users with little to no SQL knowledge, while its fast performance makes it a valuable tool for professionals looking to optimize their workflows.

EverSQL is a trusted AI SQL generator used by many professionals for automatic SQL optimization and indexing. EverSQL uses smart, AI-based algorithms to rewrite and index your SQL queries, delivering optimized performance.

• Automatic SQL Query Optimization : EverSQL uses AI to automatically rewrite and index your SQL queries, improving their efficiency.
• Ongoing Performance Insights : EverSQL's non-intrusive sensor tracks your database performance on an ongoing basis, generating optimization insights that are easy to understand and implement.
• Database Compatibility : EverSQL works with a wide range of databases, making it a versatile tool for different database environments.

Our Take: EverSQL stands out for its focus on SQL query optimization. Its ability to automatically rewrite and index your SQL queries can greatly enhance your database performance. Its ongoing performance insights also make it a valuable tool for continuous database monitoring and optimization.

5. LogicLoop

LogicLoop leverages AI to assist you in writing SQL queries. It uses Natural Language Processing and other advanced techniques to understand the intent behind a query and automatically generate the corresponding SQL query.

• AI-Assisted SQL Query Generation : LogicLoop uses AI to generate SQL queries based on user prompts. It supports various operations including data exploration and ad hoc analysis.
• Query Optimization : LogicLoop optimizes queries for performance, resulting in faster query execution times and efficient use of resources.
• Database Connection : LogicLoop allows you to connect your database to create SQL queries based on your specific data schema.

Our Take: LogicLoop is a comprehensive tool that makes SQL query writing a breeze. Its use of AI for query generation and optimization makes it an invaluable tool for any data analyst or developer. Its ability to connect to your database and generate queries based on your specific data schema makes it a highly personalized tool for SQL query generation.

6. Outerbase

Outerbase is an AI-powered database platform that allows users to view, query, visualize, and edit data effortlessly. It offers a user-friendly interface to navigate and collaborate on data, making it a valuable tool for teams.

• AI Copilot (EZQL) : Outerbase introduces EZQL, an AI data analyst that helps generate SQL by simply asking your database a question.
• Commands : Automate entire workflows, APIs, and more with Commands, offering control over various database operations.
• Plugins : Customize your database experience with plugins from their marketplace or even build your own to suit your needs.
• Instant Insights : Automatically generate charts and dashboards from your queries for quick data visualization.

Our Take: Outerbase's unique set of features like EZQL, Commands, and Plugins makes it a highly customizable and user-friendly tool. Its focus on collaboration and data visualization can be a boon for teams working together on data analysis projects.

7. AI Query

AI Query empowers users to create efficient SQL queries using simple English, bridging the gap between non-technical users and database querying. It's an excellent choice for those who want to generate SQL quickly without diving into the technical details.

• Simple English to SQL : Transform simple English into SQL queries effortlessly, making database querying accessible to everyone.
• SQL to English Translator : Understand complex SQL in simple English with the help of the AI-powered SQL to English translator.
• Database Schema Definition : Easily define your database tables using a user-friendly dashboard interface.
• Supported Database Engines : Although some are planned, a variety of database engines are supported, making it a versatile choice.
• Simple Pricing : With a straightforward pricing model, it caters to both monthly and yearly users with different benefits like unlimited SQL query generation, SQL explanations, and more.

Our Take: AI Query is a robust yet user-friendly tool that lowers the barrier to database querying. Its pricing model and support for multiple database engines make it a practical choice for various users, from individuals to small businesses.

Frequently Asked Questions

What is an AI SQL Generator?

An AI SQL Generator is a tool that uses artificial intelligence to generate SQL queries from user prompts. These tools leverage Natural Language Processing and other advanced techniques to understand the intent behind a query and generate the corresponding SQL query.

Why should I use an AI SQL Generator?

AI SQL Generators can save you significant time and effort by automating the process of writing SQL queries. They can handle complex queries, optimize queries for performance, and even detect and correct errors in your query syntax.

Are AI SQL Generators safe to use?

Yes, most AI SQL Generators adhere to strict privacy and security standards to ensure your data remains safe and confidential. Some tools even offer desktop versions that allow you to run queries and visualize your data locally, ensuring enhanced privacy and security.

Can AI SQL Generators handle complex queries?

Yes, many AI SQL Generators are capable of handling complex SQL queries. However, for more complex queries, you may need to provide more specific information or feedback to the tool.

In conclusion, the best AI SQL generators not only automate the process of writing SQL queries but also enhance the efficiency and accuracy of your data analysis tasks.

Whether you're a beginner looking to learn SQL or a professional seeking to optimize your workflows, these tools can be invaluable assets in your data management toolkit.

All AI SQL Generators

• Accessibility Policy
• Skip to content
• QUICK LINKS
• Oracle Cloud Infrastructure
• Oracle Fusion Cloud Applications
• Oracle Database
• Download Java
• Careers at Oracle

• Artificial Intelligence

Generate SQL Queries from Natural Language Using Oracle Autonomous Database Select AI

AI solution topics

Introduction

Prerequisites and setup, getting started, additional resources.

See all AI solutions

Complex problems often are resolved by integrating multiple technologies. This solution explores the integration of cutting-edge AI technologies, including Cohere AI with Oracle Autonomous Database Select AI, to craft responsive SQL queries or engage in conversational interactions. With the aid of the DBMS_CLOUD_AI package, we configure the translation of natural language prompts into SQL statements, seamlessly bridging the gap between user input and database interaction.

Oracle APEX is used to build a user-friendly interface, letting users easily input queries and visualize AI-generated results. Additionally, we leverage Oracle Machine Learning Notebooks to execute Select AI SQL queries, further enhancing our analytical capabilities.

This solution showcases a practical example of Cohere AI’s synergy with Oracle Autonomous Database , using customer information and hospital data tables to illustrate the potential of AI-driven database interactions.

• Oracle Cloud account— sign-up page
• Oracle Cloud Infrastructure— documentation
• Cohere developer guide— documentation
• Oracle APEX— documentation
• Oracle Autonomous Database— documentation
• Detailed steps and sample code on GitHub
• Blog: Natural Language to SQL Generation on Autonomous Database

SQL for Data Analysis: 15 Practical Exercises with Solutions

• sql practice
• data analysis

Table of Contents

Store Database Overview

Exercise 1: all products, exercise 2: products with the unit price greater than 3.5, exercise 3: products with specific conditions for category and price, exercise 4: products and categories, exercise 5: purchases and products, exercise 6: purchases and categories, exercise 7: sorted categories, exercise 8: employees sorted by birth date, exercise 9: products sorted by the number of units, exercise 10: the average unit price for each category, exercise 11: the  number of customers in cities, exercise 12: the number of discontinued products, exercise 13: employees with an unknown hire date, exercise 14: number of employees with unknown birth and hire dates, exercise 15: percentage of money spent by the customer on purchase, ready for more sql exercises.

Are you new to the world of SQL and eager to unlock the power of data analysis?. In this article, we'll improve our SQL skills through 15 practical, hands-on exercises designed specifically for beginners. Because when it comes to SQL, practice truly makes perfect!

We’re going to shine a spotlight on the critical domain of data analysis, where SQL takes center stage. SQL, or Structured Query Language, plays a pivotal role in data analysis. It provides a robust framework for querying, transforming, and extracting invaluable insights from databases. If you're eager to delve deeper into the world of SQL and become a master of data analysis, you can explore additional information and resources in our article Learn SQL for Data Analysis .

What we aren’t going to do is bore you with theory. Instead, we firmly believe in the power of learning through hands-on experience. That's why we've curated a collection of 15 beginner-friendly SQL exercises that immerse you in the art of data analysis – all while utilizing a real-world dataset from a store.

These exercises are drawn from our comprehensive course, Basic SQL Practice: A Store , which offers a total of 169 interactive online exercises. The exercises in the article are grouped into different topics: single table queries, JOIN queries, ORDER BY queries, GROUP BY exercises, and other features. If you enjoy these exercises, you'll undoubtedly want to explore the course for a more extensive learning experience.

Now, get ready to roll up your sleeves and fully immerse yourself in the realm of SQL-powered data analysis!

The store database consists of six tables, each serving a unique purpose and structured for specific functions. These tables are pivotal to the SQL exercises at hand, and gaining a comprehensive understanding of their components is important. Below, we will look at a brief description of each table.

• employee : Contains employee details like ID, name, birth date, address, city, country, and immediate supervisor.
• customer : Stores customer information, including customer ID, name, company, email, address, city, and country.
• purchase : Records order details, including order ID, customer ID (who placed the order), employee (who serviced the order), total price, and purchase and shipment details.
• purchase_item : Connects purchases with products via ID, product, unit price, and quantity.
• category : Provides insights into product categories using category ID, name, description, and parent category ID.
• product : Lists store products and includes product ID, product name, category ID, quantity per unit, unit price, units in stock, and product status.

Now that we have a better understanding of the tables in the store database, let's dive into some SQL exercises to help sharpen your data analysis skills.

Data Analysis SQL Exercises

Since practice is essential for developing and refining your SQL skills, these exercises will serve as valuable tools. We've even created a SQL for Data Analysis Cheat Sheet to make things easier for you! You might want to keep it handy on your journey through the following 15 SQL exercises.

Single Table Queries

In this section, we are going to focus on SQL queries involving a single table. These exercises will help you master the foundational art of retrieving, filtering, and working with data within a single dataset.

Exercise : Display all data present in the product table.

Solution explanation: This simple query retrieves all the data from the product table. The asterisk ( * ) is used to indicate that we want to select all available columns in the table, essentially fetching all the data. After the FROM keyword, we give the name of the table we’re selecting from (here, the table product ).

Exercise: Display product names for products with a unit price greater than or equal to 3.5.

Solution explanation: Using the WHERE clause, we filter for product_names with a unit price greater than or equal to 3.5. The WHERE clause restricts the rows returned by the query to only those meeting the specified criteria.

Exercise: Display data for all products in categories with an ID of 1 (Food) or 5 (Fruits and Vegetables) and with a unit price above 3.5.

Solution explanation: This query uses both the AND and OR operators to create complex conditions that filter products based on category and unit_price .

The OR operator allows products from either category 1 (Food) or category 5 (Fruits and Vegetables), while the AND operator ensures that selected products must have a unit price greater than 3.5. The use of brackets enhances query readability.

JOIN Queries

Joins are a fundamental concept in SQL. They are crucial for data analysis because they allow you to combine and analyze data from different sources, enabling comprehensive insights into your datasets. For more practice exercises involving joins, refer to our article SQL Joins: 12 Practice Questions with Detailed Answers .

Exercise: Select product names along with their categories. Display two columns: product_name and category_name .

Solution explanation: In this exercise, an INNER JOIN combines data from the product and category tables. An INNER JOIN selects only the rows that have matching values in both tables. This ensures that only products with corresponding categories are retrieved, creating a meaningful and accurate result.

The ON part of the query defines the relationship between the two tables. Additionally, the use of aliases ( AS category_name ) allows us to rename the columns as required by the question.

Exercise: For each purchase, display the purchase ID, product name, unit price at the time of purchase, and quantity of each product's items.

Solution explanation: This query combines data from the purchase_item and product tables, allowing us to display relevant purchase information. In SQL, INNER JOIN and JOIN are often used interchangeably, but they serve the same purpose: retrieving matching rows from both tables.

Exercise: For each purchase, display all product categories bought in this purchase. Show each category only once for each purchase.

Solution explanation: In this exercise, we're joining three tables: purchase_item , product , and category . Joining more than two tables is achieved by sequentially connecting each table through their related columns. If you want to learn more about joining multiple tables, read How to Join 3 Tables (or More) in SQL .

The use of the DISTINCT keyword here ensures that each product category appears only once for each purchase, providing a clear and concise list of categories associated with every purchase. This can be especially useful when you want to avoid redundant information in your result set.

ORDER BY Queries

Sorting data is a crucial step in data analysis because it allows you to organize information in a structured and meaningful way. The following exercises will teach you how to arrange data meaningfully for reporting and analysis purposes.

Exercise: Display all categories' data. Sort the categories by name in ascending (A to Z) order.

Solution explanation: In this exercise, we use the ORDER BY clause to sort the results from the category table in ascending order based on the name column. This arrangement allows you to view the categories in alphabetical order. The ASC keyword is optional here; it represents the default sorting order, which is ascending.

Exercise: Show employees’ last names, first names, and birthdates. Sort results by employee age in ascending order ( Hint: Use the birth_date column ).

Solution explanation: Since there is no age column in our table, we sort employees by birth_date in descending order using the DESC keyword. Sorting by birth_date in descending order allows you to view employees from the youngest to the oldest.

The use of DESC is crucial here, it reverses the default ascending sorting order and achieves the desired outcome.

Exercise: Display data for all products, sorting the results by the number of units in descending order and product name in ascending order.

Solution explanation: This query orders products first by units_in_stock in descending order, and then by product_name in ascending order. The first sorting condition ensures that products with the most units in stock appear at the top; products with the same number of units are further sorted alphabetically by name.

GROUP BY Queries

In this section, we explore SQL queries that involve grouping data for analysis. GROUP BY is essential for aggregating and summarizing information based on specific criteria. Check out GROUP BY in SQL Explained for more information.

Exercise: For each product category, show its name and find the average unit price. Display two columns: name and average_unit_price .

Solution explanation: In this exercise, we're calculating the average unit price for each product category. The query joins the product and category tables to associate products with their respective categories. The GROUP BY clause is then used to group the data by category_name . Using AVG() , we can calculate the average unit price within each category – giving us insights into the price distribution across different product categories.

Exercise: Count how many customers live in each city except for Knoxville and Stockton. Sort the results by the city name in ascending order. Display two columns: city and customers_quantity .

Solution explanation: In this query, we use the WHERE clause with the <> operator (which means not equal to ) to filter out records with the city name of Knoxville or Stockton . This filtering ensures that the analysis covers all cities except for the specified ones.

The COUNT() function then calculates the customer count for each of the remaining cities. The GROUP BY clause groups the data by city name, enabling us to count customers for each city.

Finally, the results are ordered in ascending order by city, providing a clear and organized view of customer distribution across various cities.

Exercise: For each category, find the number of discontinued products. Show only the categories with at least three discontinued products. Sort the rows by the number of discontinued products in descending order. Display two columns: name (the name of the category) and discontinued_products_number .

Solution explanation: In this query, we utilize the HAVING clause to filter categories with at least three discontinued products. The HAVING clause is applied after the GROUP BY operation and allows us to filter the aggregated results based on the specific condition. For more information on using HAVING , read our articles The SQL HAVING Clause Explained and HAVING vs. WHERE in SQL: What You Should Know .

Additionally, you'll notice the use of the COUNT() function within the ORDER BY clause. This allows us to sort the rows based on the count of discontinued products in descending order.

The ORDER BY clause is flexible and can include aggregate functions , making it a powerful tool for arranging data based on aggregated values.

Other SQL Features

In this section, we delve into additional SQL features to expand your toolkit. We'll explore working with NULL values and cleaning up data using the ROUND() function . These features are crucial for handling complex data analysis scenarios.

Exercise: Display the last and first names of employees with an unknown hire date.

Solution explanation: In this query, the WHERE clause with hire_date IS NULL allows us to filter and select records where the hire date is missing. This type of query can be a crucial analysis for identifying incomplete employee records or data entry errors within the dataset.

Exercise: Count the number of employees with both unknown birth and hire dates.

Solution explanation: In this query, we again use the IS NULL condition to filter for rows where the birth_date and hire_date are both missing. This allows us to COUNT() only those employees who lack both birth and hire date information.

Exercise: For each customer who made a purchase, display the ID of each purchase made by this customer and the percentage of money spent on that purchase relative to all the money spent by that customer.

Solution explanation: In this solution, we use two queries. The main query incorporates a subquery within the SELECT statement to calculate the SUM() of total prices for purchases made by the same customer.

The percentage is then calculated using the provided formula and the ROUND() function is applied to round the result. This query is a powerful tool for gaining insights into the purchasing behavior of each customer.

Mastering SQL is an essential skill for data analysis – and the best way to learn it is by doing. In this article we went through a selection of 15 beginner-friendly SQL exercises drawn from our comprehensive course, Basic SQL Practice: A Store .

But these exercises are just the beginning. If you're eager for a more extensive learning experience, we invite you to explore our complete course that features 169 interactive exercises. For those who crave even greater challenges, this course is just one of the nine courses available on the SQL Practice learning path – each offering an engaging format.

We also have our SQL Monthly Practice track, which is regularly updated with fresh exercises to keep your skills sharp and up-to-date with the latest SQL developments.

Immerse yourself in more SQL exercises and you'll master the art of data analysis. Your journey to becoming an SQL expert begins with continuous practice. So, keep exploring and honing your skills. Success is just around the corner!

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query

• Have any question ?
• +91 8106-920-029
• +91 6301-939-583
• [email protected]
• Live Sessions

SQL: Importance and Sample Problems

59 Comment(s) Loading... Search

How to utilise appliedaicourse, python for data science introduction, python for data science: data structures, plotting for exploratory data analysis (eda), linear algebra, probability and statistics, dimensionality reduction and visualization:, pca(principal component analysis), (t-sne)t-distributed stochastic neighbourhood embedding, case study 1: quora question pair similarity problem, case study 2: personalized cancer diagnosis, case study 4:taxi demand prediction in new york city, case study 5: stackoverflow tag predictor, case study 6: microsoft malware detection, case study 9:netflix movie recommendation system (collaborative based recommendation), opencv using python, case study 10: self driving car, case study 13: semantic search engine for q&a [design + code], statistical testing and experiments(recorded live sessions), module 1: live sessions, module 2: live sessions, module 7: live sessions, module 8: live sessions, module 9: live sessions, machine learning high-level design, sample interview and conceptual questions [audio], module 10: live sessions.

Navigation Menu

Search code, repositories, users, issues, pull requests..., provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications You must be signed in to change notification settings

Assignment on IMDB database using sqlite3 and pandas

GopiSumanth/SQL

Folders and files.

Repository files navigation

Assignment on IMDB database using sqlite3 and pandas This repository contains Db-IMDB database and its schema is in db_schema file. Required SQL commands are present in mySql Commands file. It is kind of my notes on SQL The Assignment questions are present in sql_questions file and the solutions are present in solutions.ipynb

NOTE: If anyone found better way to solve the assignment questions kindly let me know. My email: [email protected]

• Jupyter Notebook 100.0%

• SQL Cheat Sheet
• SQL Interview Questions
• MySQL Interview Questions
• PL/SQL Interview Questions
• Learn SQL and Database

SQL Exercises : SQL Practice with Solution for Beginners and Experienced

SQL ( Structured Query Language ) is a powerful tool used for managing and manipulating relational databases. Whether we are beginners or experienced professionals, practicing SQL exercises is important for improving your skills. Regular practice helps you get better at using SQL and boosts your confidence in handling different database tasks.

So, in this free SQL exercises page, we’ll cover a series of SQL practice exercises covering a wide range of topics suitable for beginners , intermediate , and advanced SQL learners. These exercises are designed to provide hands-on experience with common SQL tasks, from basic retrieval and filtering to more advanced concepts like joins window functions , and stored procedures.

Table of Content

SQL Exercises for Practice

Sql practice exercises for beginners, sql practice exercises for intermediate, sql practice exercises for advanced, more questions for practice.

Practice SQL questions to enhance our skills in database querying and manipulation. Each question covers a different aspect of SQL , providing a comprehensive learning experience.

We have covered a wide range of topics in the sections beginner , intermediate and advanced .

• Basic Retrieval
• Arithmetic Operations and Comparisons:
• Aggregation Functions
• Group By and Having
• Window Functions
• Conditional Statements
• DateTime Operations
• Creating and Aliasing
• Constraints
• Stored Procedures:
• Transactions

let’s create the table schemas and insert some sample data into them.

Create Sales table

Create Products table

This hands-on approach provides a practical environment for beginners to experiment with various SQL commands, gaining confidence through real-world scenarios. By working through these exercises, newcomers can solidify their understanding of fundamental concepts like data retrieval, filtering, and manipulation, laying a strong foundation for their SQL journey.

1. Retrieve all columns from the Sales table.

Explanation:

This SQL query selects all columns from the Sales table, denoted by the asterisk (*) wildcard. It retrieves every row and all associated columns from the Sales table.

2. Retrieve the product_name and unit_price from the Products table.

This SQL query selects the product_name and unit_price columns from the Products table. It retrieves every row but only the specified columns, which are product_name and unit_price.

3. Retrieve the sale_id and sale_date from the Sales table.

This SQL query selects the sale_id and sale_date columns from the Sales table. It retrieves every row but only the specified columns, which are sale_id and sale_date.

4. Filter the Sales table to show only sales with a total_price greater than $100.

This SQL query selects all columns from the Sales table but only returns rows where the total_price column is greater than 100. It filters out sales with a total_price less than or equal to $100.

5. Filter the Products table to show only products in the ‘Electronics’ category.

This SQL query selects all columns from the Products table but only returns rows where the category column equals ‘Electronics’. It filters out products that do not belong to the ‘Electronics’ category.

6. Retrieve the sale_id and total_price from the Sales table for sales made on January 3, 2024.

This SQL query selects the sale_id and total_price columns from the Sales table but only returns rows where the sale_date is equal to ‘2024-01-03’. It filters out sales made on any other date.

7. Retrieve the product_id and product_name from the Products table for products with a unit_price greater than $100.

This SQL query selects the product_id and product_name columns from the Products table but only returns rows where the unit_price is greater than $100. It filters out products with a unit_price less than or equal to $100.

8. Calculate the total revenue generated from all sales in the Sales table.

This SQL query calculates the total revenue generated from all sales by summing up the total_price column in the Sales table using the SUM() function.

9. Calculate the average unit_price of products in the Products table.

This SQL query calculates the average unit_price of products by averaging the values in the unit_price column in the Products table using the AVG() function.

10. Calculate the total quantity_sold from the Sales table.

This SQL query calculates the total quantity_sold by summing up the quantity_sold column in the Sales table using the SUM() function.

11. Retrieve the sale_id, product_id, and total_price from the Sales table for sales with a quantity_sold greater than 4.

This SQL query selects the sale_id, product_id, and total_price columns from the Sales table but only returns rows where the quantity_sold is greater than 4.

12. Retrieve the product_name and unit_price from the Products table, ordering the results by unit_price in descending order.

This SQL query selects the product_name and unit_price columns from the Products table and orders the results by unit_price in descending order using the ORDER BY clause with the DESC keyword.

13. Retrieve the total_price of all sales, rounding the values to two decimal places.

This SQL query calculates the total sales revenu by summing up the total_price column in the Sales table and rounds the result to two decimal places using the ROUND() function.

14. Calculate the average total_price of sales in the Sales table.

This SQL query calculates the average total_price of sales by averaging the values in the total_price column in the Sales table using the AVG() function.

15. Retrieve the sale_id and sale_date from the Sales table, formatting the sale_date as ‘YYYY-MM-DD’.

This SQL query selects the sale_id and sale_date columns from the Sales table and formats the sale_date using the DATE_FORMAT() function to display it in ‘YYYY-MM-DD’ format.

16. Calculate the total revenue generated from sales of products in the ‘Electronics’ category.

This SQL query calculates the total revenue generated from sales of products in the ‘Electronics’ category by joining the Sales table with the Products table on the product_id column and filtering sales for products in the ‘Electronics’ category.

17. Retrieve the product_name and unit_price from the Products table, filtering the unit_price to show only values between $20 and $600.

This SQL query selects the product_name and unit_price columns from the Products table but only returns rows where the unit_price falls within the range of $50 and $200 using the BETWEEN operator.

18. Retrieve the product_name and category from the Products table, ordering the results by category in ascending order.

This SQL query selects the product_name and category columns from the Products table and orders the results by category in ascending order using the ORDER BY clause with the ASC keyword.

19. Calculate the total quantity_sold of products in the ‘Electronics’ category.

This SQL query calculates the total quantity_sold of products in the ‘Electronics’ category by joining the Sales table with the Products table on the product_id column and filtering sales for products in the ‘Electronics’ category.

20. Retrieve the product_name and total_price from the Sales table, calculating the total_price as quantity_sold multiplied by unit_price.

This SQL query retrieves the product_name from the Sales table and calculates the total_price by multiplying quantity_sold by unit_price, joining the Sales table with the Products table on the product_id column.

These exercises are designed to challenge you beyond basic queries, delving into more complex data manipulation and analysis. By tackling these problems, you’ll solidify your understanding of advanced SQL concepts like joins, subqueries, functions, and window functions, ultimately boosting your ability to work with real-world data scenarios effectively.

1. Calculate the total revenue generated from sales for each product category.

This query joins the Sales and Products tables on the product_id column, groups the results by product category, and calculates the total revenue for each category by summing up the total_price.

2. Find the product category with the highest average unit price.

This query groups products by category, calculates the average unit price for each category, orders the results by the average unit price in descending order, and selects the top category with the highest average unit price using the LIMIT clause.

3. Identify products with total sales exceeding 30.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, calculates the total sales revenue for each product, and selects products with total sales exceeding 30 using the HAVING clause.

4. Count the number of sales made in each month.

This query formats the sale_date column to extract the month and year, groups the results by month, and counts the number of sales made in each month.

5. Determine the average quantity sold for products with a unit price greater than $100.

This query joins the Sales and Products tables on the product_id column, filters products with a unit price greater than $100, and calculates the average quantity sold for those products.

6. Retrieve the product name and total sales revenue for each product.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, and calculates the total sales revenue for each product.

7. List all sales along with the corresponding product names.

This query joins the Sales and Products tables on the product_id column and retrieves the sale_id and product_name for each sale.

8. Retrieve the product name and total sales revenue for each product.

This query will give you the top three product categories contributing to the highest percentage of total revenue generated from sales. However, if you only have one category (Electronics) as in the provided sample data, it will be the only result.

9. Rank products based on total sales revenue.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, calculates the total sales revenue for each product, and ranks products based on total sales revenue using the RANK () window function.

10. Calculate the running total revenue for each product category.

This query joins the Sales and Products tables on the product_id column, partitions the results by product category, orders the results by sale date, and calculates the running total revenue for each product category using the SUM() window function.

11. Categorize sales as “High”, “Medium”, or “Low” based on total price (e.g., > $200 is High, $100-$200 is Medium, < $100 is Low).

This query categorizes sales based on total price using a CASE statement. Sales with a total price greater than $200 are categorized as “High”, sales with a total price between $100 and $200 are categorized as “Medium”, and sales with a total price less than $100 are categorized as “Low”.

12. Identify sales where the quantity sold is greater than the average quantity sold.

This query selects all sales where the quantity sold is greater than the average quantity sold across all sales in the Sales table.

13. Extract the month and year from the sale date and count the number of sales for each month.

14. Calculate the number of days between the current date and the sale date for each sale.

This query calculates the number of days between the current date and the sale date for each sale using the DATEDIFF function.

15. Identify sales made during weekdays versus weekends.

This query categorizes sales based on the day of the week using the DAYOFWEEK function. Sales made on Sunday (1) or Saturday (7) are categorized as “Weekend”, while sales made on other days are categorized as “Weekday”.

This section likely dives deeper into complex queries, delving into advanced features like window functions, self-joins, and intricate data manipulation techniques. By tackling these challenging exercises, users can refine their SQL skills and tackle real-world data analysis scenarios with greater confidence and efficiency.

1. Write a query to create a view named Total_Sales that displays the total sales amount for each product along with their names and categories.

This query creates a view named Total_Sales that displays the total sales amount for each product along with their names and categories.

2. Retrieve the product details (name, category, unit price) for products that have a quantity sold greater than the average quantity sold across all products.

This query retrieves the product details (name, category, unit price) for products that have a quantity sold greater than the average quantity sold across all products.

3. Explain the significance of indexing in SQL databases and provide an example scenario where indexing could significantly improve query performance in the given schema.

With an index on the sale_date column, the database can quickly locate the rows that match the specified date without scanning the entire table. The index allows for efficient lookup of rows based on the sale_date value, resulting in improved query performance.

4. Add a foreign key constraint to the Sales table that references the product_id column in the Products table.

This query adds a foreign key constraint to the Sales table that references the product_id column in the Products table, ensuring referential integrity between the two tables.

5. Create a view named Top_Products that lists the top 3 products based on the total quantity sold.

This query creates a view named Top_Products that lists the top 3 products based on the total quantity sold.

6. Implement a transaction that deducts the quantity sold from the Products table when a sale is made in the Sales table, ensuring that both operations are either committed or rolled back together.

The quantity in stock for product with product_id 101 should be updated to 5.The transaction should be committed successfully.

7. Create a query that lists the product names along with their corresponding sales count.

This query selects the product names from the Products table and counts the number of sales (using the COUNT() function) for each product by joining the Sales table on the product_id. The results are grouped by product name using the GROUP BY clause.

8. Write a query to find all sales where the total price is greater than the average total price of all sales.

The subquery (SELECT AVG(total_price) FROM Sales) calculates the average total price of all sales. The main query selects all columns from the Sales table where the total price is greater than the average total price obtained from the subquery.

9. Analyze the performance implications of indexing the sale_date column in the Sales table, considering the types of queries commonly executed against this column.

Query without indexing:.

Query with Indexing:

This format clearly displays the operations and details of the query execution plan before and after indexing.

Without indexing, the query performs a full table scan, filtering rows based on the sale date, which is less efficient. With indexing, the query uses the index to quickly locate the relevant rows, significantly improving query performance.

10. Add a check constraint to the quantity_sold column in the Sales table to ensure that the quantity sold is always greater than zero.

All rows in the Sales table meet the condition of the check constraint, as each quantity_sold value is greater than zero.

11. Create a view named Product_Sales_Info that displays product details along with the total number of sales made for each product.

This view provides a concise and organized way to view product details alongside their respective sales information, facilitating analysis and reporting tasks.

12. Develop a stored procedure named Update_Unit_Price that updates the unit price of a product in the Products table based on the provided product_id.

The above SQL code creates a stored procedure named Update_Unit_Price. This stored procedure takes two parameters: p_product_id (the product ID for which the unit price needs to be updated) and p_new_price (the new unit price to set).

13. Implement a transaction that inserts a new product into the Products table and then adds a corresponding sale record into the Sales table, ensuring that both operations are either fully completed or fully rolled back.

This will update the unit price of the product with product_id 101 to 550.00 in the Products table.

14. Write a query that calculates the total revenue generated from each category of products for the year 2024.

When you execute this query, you will get the total revenue generated from each category of products for the year 2024.

If you’re looking to sharpen your SQL skills and gain more confidence in querying database s, consider delving into these articles. They’re packed with query-based SQL questions designed to enhance your understanding and proficiency in SQL .

By practicing with these exercises, you’ll not only improve your SQL abilities but also boost your confidence in tackling various database-related tasks. The Questions are as follows:

• How to Insert a Value that Contains an Apostrophe in SQL?
• How to Select Row With Max Value in SQL?
• How to Efficiently Convert Rows to Columns in SQL?
• How To Use Nested Select Queries in SQL
• How to Select Row With Max Value on a Column in SQL?
• How to Specify Condition in Count() in SQL?
• How to Find the Maximum of Multiple Columns in SQL?
• How to Update Top 100 Records in SQL?
• How to Select the Last Records in a One-To-Many Relationship Using SQL Join
• How to Join First Row in SQL?
• How to Insert Row If Not Exists in SQL?
• How to Use GROUP BY to Concatenate Strings in SQL?
• How Inner Join works in LINQ to SQL
• How to Get the Identity of an Inserted Row in SQL
• How to Declare a Variable in SQL?

Mastering SQL requires consistent practice and hands-on experience. By working through these SQL practice exercises , you’ll strengthen your skills and gain confidence in querying relational databases.

Whether you’re just starting or looking to refine your expertise, these exercises provide valuable opportunities to hone your SQL abilities. Keep practicing , and you’ll be well-equipped to tackle real-world data challenges with SQL.

Please Login to comment...

Similar reads.

• 105 Funny Things to Do to Make Someone Laugh
• Best PS5 SSDs in 2024: Top Picks for Expanding Your Storage
• Best Nintendo Switch Controllers in 2024
• Xbox Game Pass Ultimate: Features, Benefits, and Pricing in 2024
• #geekstreak2024 – 21 Days POTD Challenge Powered By Deutsche Bank

Improve your Coding Skills with Practice

What kind of Experience do you want to share?