ccbp sql assignment answers

Assignment 1 « SQL »

Due: 11:59pm 02/11/2021 et.

Getting the Stencil

You can click here to get the stencil code for Homework 1. Reference this guide for more information about Github and Github Classroom.

The data is located in the data folder. To ensure compatibility with the autograder, you should not modify the stencil unless instructed otherwise. For this assignment, please write each of your queries to its corresponding SQL file. Failing to do so may hinder with the autograder and result in a low grade.

Virtual Environment

1. linux/osx.

You need to set up a virtual environment to run Python assignments, if you want to work on your local machine.

Our Gradescope Autograder, virtual environment guide and create_venv.sh script in your stencil code runs on Python 3.7 . You might run into strange issues setting up the virtual environment / working on future assignments if you don't have the right version of Python. Please refer to this guide for instructions to check whether you have the right Python version and how to install the correct one if you do not.

You can run ./create_venv.sh (or bash create_venv.sh ) from your assignment directory to create the virtual environment. Run chmod 775 create_venv.sh if you get a permission denied error.

If nothing is printed out in your terminal besides the message "created cs1951a environment" , congratulations! You have successfully installed the virtual environment for the course on your machine! If any error message is printed out, please utilize TA Hours / Piazza as a resource to resolve the issue(s).

From now on, whenever you want to activate your virtual environment for our assignments, you can simply type the following in your terminal:

• Working locally : cs1951a_venv (or source ~/cs1951a_venv/bin/activate ).
• Working on a department machine through ssh : source /course/cs1951a/venv/bin/activate

You can refer to this guide for more information about installing our course's virtual environment. If you have successfully executed our create_venv.sh script above and have the right Python version, you should not worry about reading in-depth into this resource.

If you use a Windows machine, as there are different ways students use Terminal/File Transfer to complete assignments, we unfortunately do not have a robust, uniform script/guide to install a virtual environment locally on Windows machines.

We strongly recommend running your assignment on the department machine. Git is installed on the department machines, so you should also be able to git clone your assignments onto the department machine.

The recommended workflow is to work on your assignment locally, and either (1) use version control, or (2) use SCP/PuTTY to transfer your files to the department machine. To run your assignments on the department machine, make sure to enable the course virtual environment (using source /course/cs1951a/venv/bin/activate ) before running any Python code! Refer to this resource for more support regarding file transfer, and this with respect to version control.

If you have successfully installed a Python3.7 Virtual Environment on your machine in the past: We have included the requirements.txt dependencies file on the course website ( here ). If it is helpful, we also have a virtual environment setup guide here .

If you are working locally, you can check if SQLite is installed already by running sqlite3 -version in your terminal. You can refer to this guide to install SQLite on your machine. SQLite is installed on all department machines. It can be accessed from the command line using sqlite3 .

Running sqlite3 somedb.db from your terminal will launch an environment that will allow you to type your SQL queries directly into the terminal. You can exit this environment by pushing Ctrl+D or by typing .exit and pressing enter.

As a more explicit example, to open a sql environment where you can query the movies.db database, you can type:

$ sqlite3 movies.db

To execute a SQL statement that you have saved in a solution file, you can run the following command:

For more information on using SQLite from the command line, see http://www.sqlite.org/sqlite.html . Additionally, we have provided very helpful hints for most of the problems; you should be able to use these as a starting point if you get stuck before looking up additional information online.

Some useful tools you can use to view the content in a database: SQLite Viewer and SQLTools + SQLite packages (if you are developing on VS Code ).

Part 1: Starting Off!

This part of the assignment builds off the exercises you completed in the lab. If you have not yet completed the lab, please do so before starting this assignment. There are some really useful hints and examples you can borrow from the lab for this assignment. The database and schema are described again below, but are the same from the lab.

We have provided a database named people.db with the name, age, ID, and occupation of some Brown students and alumni. Here is the schema:

In the people_friends table, each ( ID1 , ID2 ) pair indicates that the particular person with ID1 is friends with the person with ID2 (and vice versa). The friendship is mutual, and if ( ID1 , ID2 ) is in the table, it is guaranteed that ( ID2 , ID1 ) exists in the table.

In the people_likes table, each ( ID1 , ID2 ) pair indicates that the student or alumni with ID1 likes the person with ID2 . The ( ID1 , ID2 ) pair in the table does not guarantee that the ( ID2 , ID1 ) pair also exists in the table.

Your job is to write SQL queries for the data being requested:

Hint: Use a LEFT JOIN ! The following website is quite useful: http://blog.codinghorror.com/a-visual-explanation-of-sql-joins/

Hint: The LIMIT statement will come in handy!

Hint: You'll need to take a look at the HAVING function.

• (4 points) Write a SQL statement that returns the distinct name and age of all people who are liked by anyone younger than them. Results should be ordered by name (A-Z). Save the query to part1_problem4.sql .
• (4 points) Write a SQL statement to find pairs (A, B) such that person A likes person B, but A is not friends with B. The query should return 4 columns: ID of person 1, name of person 1, ID of person 2 and name of person 2. Results should be ordered by ID1 (ascending), then ID2 (ascending). Save the query to part1_problem5.sql

Time to join stuff!

Part 2: Getting Harder!

For this part of the assignment, you will be using the TMDB Movie Dataset, which has been exported to the movies.db database. The database schema is as follows:

We encourage you to use the WITH operator, which lets you divide your query into separate queries. As an example, we can define a subquery and use it in another query as follows (there is also an example in the lab!):

You can add days to a particular day by using the date function. For example, in order to add 3 days to to '2012-07-16', you can use date('2012-07-16', '+3 days')

Hint: The UNION statement should come in handy.

• (10 points) Write a SQL query to count the number of movies that start with "The", end with a "2" or contain the word "shark". Your query should be case insensitive and return one column with one entry. You should return a single value.

Hint: You may want to look into CASE statements and the LIKE operator. (Lab!)

• (10 points) Write a SQL query to select the original_title of all movies and a column where there is a 1 if there exists another movie that has the same vote average and the same runtime as that movie, and a 0 otherwise. Results should be ordered by original_title (A-Z).

Hint: Look into the BETWEEN statement and how it can be used in a join.

Another Hint: Do not modify the current database by using UPDATE . Take a look at the CASE operation example from the lab.

• (10 points) Write a SQL query that finds the original_title , release_date and revenue of all the movies whose revenue exceeded the average revenue of all the movies released on the same day (including itself). Results should be ordered by release_date (ascending), and then revenue (descending).

(10 points) Write a SQL query that, for each original_language that has more than 2 movies , finds the number of movies that were reviewed as 'poor' and the number of movies that were reviewed as 'good'.

Like in the 4th question, you will need to look at the scores table to see what the review categories are and use the vote_average field of a movie to determine which review category it falls under. Your query should return 3 columns ( original_language , num_poor which is the number of 'poor' movies for that language, and num_good which should be the number of 'good' movies for the language). Your results should be ordered by number of 'good' movies (descending) and then number of 'poor' movies (ascending). Remember to only include languages that have more than 2 movies!

Hint: Refer to the examples from the lab!

Part 3: Optimization

We have provided you with the athletes.db database, although querying it is not necessary at all. The schema is as follows:

For the query below , explain why the given query might not be the most efficient way to accomplish the task. Write out an optimized version of the query in writeup.txt . Explain what steps you took to optimize it and why your version would be more efficient.

(6 points) The SQL query to optimize is as follows:

(4 points) Consider two tables. Table A is very long with 1 billion rows and 5 columns. Table B is very wide with 1000 rows and 10,000 columns. If we were to join the two tables and want to make sure the join is performant, how should we best filter the tables? Assume that we can select from each table and then join the results. Specifically, state the table in which we should use WHERE heavily and the table in which we should be careful about what values we use in our SELECT.

Part 4: Datasets & Society

• Demonstrate how the design of database schemas can embed social values and/or inflict harm
• Highlight a proposed process for dataset creators that raises a wide range of social and ethical concerns
• Reflect on the possibilities and limitations of this process
• Read When Binary Code Won’t Accommodate Nonbinary People
• Read Section 1 (Introduction and Objectives), Section 3 (Questions and Workflow), and Section 4 (Impact and Challenges) of the paper Datasheets for Datasets .
• Answer the questions below in writeup.txt . Each response should be thoughtful, provide justification for your claims, and be concise but complete. See the response guide for more guidance.
• Name one context for which it would be beneficial to add a column to people_likes that measures the strength of the feelings or add a column to people_friends that measures the strength of the friendship. Name one context for which those additions could be harmful.
• For example: The questions “What mechanisms or procedures were used to collect the data? How were these mechanisms or procedures validated?” could have helped identify and prevent errors in automatic COVID-19 data collection in England. Public Health England used an outdated Excel file format in their automatic process to pull COVID-19 data together, leading nearly 16,000 coronavirus cases to go unreported. If the dataset creator had thought of procedures to validate this process ahead of time, they could have identified the loss of data earlier and prevented the threat to public health efforts. (Source: https://www.bbc.com/news/technology-54423988 )
• (4 points) Identify a social, ethical, or political issue that is raised in “When Binary Code Won’t Accomodate Nonbinary People” but is not addressed by the proposed datasheet system. Propose a modification or addition to the datasheet system that helps address this issue. Your proposal could involve new sections, questions, external infrastructure, incentives, and more! Explain how your proposal would help address the issue you identified.

Additional Information

• Google’s star AI ethics researcher, one of a few Black women in the field, says she was fired for a critical email
• Timnit Gebru’s Exit From Google Exposes a Crisis in AI
• Ousted Black Google Researcher: 'They Wanted To Have My Presence, But Not Me Exactly'

After finishing the assignment (and any assignment in the future), run python3 zip_assignment.py in the command line from your assignment directory, and fix any issues brought up by the script.

After the script has been run successfully, you should find the file sql-submission-1951A.zip in your assignment directory. Please submit this zip file on Gradescope under the respective assignment.

(If you have not signed up for Gradescope already, please refer to this guide .)

Made with ♥ by Jens and Esteban, updated for Spring 2021 by Yuchen, Sunny and Nazem.

The socially responsible computing component was designed by Lena and Gaurav.

Movie Database from: https://www.kaggle.com/tmdb/tmdb-movie-metadata

• SQL Tutorial
• SQL - Overview
• SQL - RDBMS Concepts
• SQL - Databases
• SQL - Syntax
• SQL - Data Types
• SQL - Operators
• SQL - Expressions
• SQL Database
• SQL - Create Database
• SQL - Drop Database
• SQL - Select Database
• SQL - Rename Database
• SQL - Show Databases
• SQL - Backup Database
• SQL - Create Table
• SQL - Show Tables
• SQL - Rename Table
• SQL - Truncate Table
• SQL - Clone Tables
• SQL - Temporary Tables
• SQL - Alter Tables
• SQL - Drop Table
• SQL - Delete Table
• SQL - Constraints
• SQL Queries
• SQL - Insert Query
• SQL - Select Query
• SQL - Select Into
• SQL - Insert Into Select
• SQL - Update Query
• SQL - Delete Query
• SQL - Sorting Results
• SQL - Create Views
• SQL - Update Views
• SQL - Drop Views
• SQL - Rename Views
• SQL Operators and Clauses
• SQL - Where Clause
• SQL - Top Clause
• SQL - Distinct Clause
• SQL - Order By Clause
• SQL - Group By Clause
• SQL - Having Clause
• SQL - AND & OR
• SQL - BOOLEAN (BIT) Operator
• SQL - LIKE Operator
• SQL - IN Operator
• SQL - ANY, ALL Operators
• SQL - EXISTS Operator
• SQL - NOT Operator
• SQL - NOT EQUAL
• SQL - IS NULL
• SQL - IS NOT NULL
• SQL - NOT NULL
• SQL - BETWEEN Operator
• SQL - UNION Operator
• SQL - UNION vs UNION ALL
• SQL - INTERSECT Operator
• SQL - EXCEPT Operator
• SQL - Aliases
• SQL - Using Joins
• SQL - Inner Join
• SQL - Left Join
• SQL - Right Join
• SQL - Cross Join
• SQL - Full Join
• SQL - Self Join
• SQL - Delete Join
• SQL - Update Join
• SQL - Left Join vs Right Join
• SQL - Union vs Join
• SQL - Unique Key
• SQL - Primary Key
• SQL - Foreign Key
• SQL - Composite Key
• SQL - Alternate Key
• SQL Indexes
• SQL - Indexes
• SQL - Create Index
• SQL - Drop Index
• SQL - Show Indexes
• SQL - Unique Index
• SQL - Clustered Index
• SQL - Non-Clustered Index
• Advanced SQL
• SQL - Wildcards
• SQL - Comments
• SQL - Injection
• SQL - Hosting
• SQL - Min & Max
• SQL - Null Functions
• SQL - Check Constraint
• SQL - Default Constraint
• SQL - Stored Procedures
• SQL - NULL Values
• SQL - Transactions
• SQL - Sub Queries
• SQL - Handling Duplicates
• SQL - Using Sequences
• SQL - Auto Increment
• SQL - Date & Time
• SQL - Cursors
• SQL - Common Table Expression
• SQL - Group By vs Order By
• SQL - IN vs EXISTS
• SQL - Database Tuning
• SQL Function Reference
• SQL - Date Functions
• SQL - String Functions
• SQL - Aggregate Functions
• SQL - Numeric Functions
• SQL - Text & Image Functions
• SQL - Statistical Functions
• SQL - Logical Functions
• SQL - Cursor Functions
• SQL - JSON Functions
• SQL - Conversion Functions
• SQL - Datatype Functions
• SQL Useful Resources

SQL - Questions and Answers

• SQL - Quick Guide
• SQL - Useful Functions
• SQL - Useful Resources
• SQL - Discussion
• Selected Reading
• UPSC IAS Exams Notes
• Developer's Best Practices
• Questions and Answers
• Effective Resume Writing
• HR Interview Questions
• Computer Glossary

SQL Questions and Answers has been designed with a special intention of helping students and professionals preparing for various Certification Exams and Job Interviews . This section provides a useful collection of sample Interview Questions and Multiple Choice Questions (MCQs) and their answers with appropriate explanations.

To Continue Learning Please Login

A Proven Program To Make You a

Your Trainers are Alumni of

and many more...

Your Seniors Got Placed. It’s Your Turn Now!

Get Ready for Your IT Career in 3 Steps

Full Stack Developer

• Programming Constructs with Instruction Flows
• Programming with Python
• Fundamentals of Computer Science

Get Your Doubts Clarified Faster than in Offline Classes

Trusted by Thousands to Become IT Professionals

1700+ Companies Hired NxtWave  R  Learners

NxtWave students carved a name for themselves in the IT industry. Hear it directly from the CEOs, CXOs and HRs of tech companies.

Mr. Sashank Gujjula, Co-founder, NxtWave, receiving the ‘Best Tech Skilling EdTech Company’ award by Times Business Awards

• Aptitude Training
• Soft Skills Training
• Resume Preparation
• AI-Powered Mock Interviews
• Mock Interviews by Tech and HR Panels
• 300+ Senior Interview Experiences
• Scheduling Interviews
• Access to Placement Portal
• Mega Offline Placement Drives
• Negotiation with companies for higher salaries

Frequently Asked Questions

What is the duration of the program.

Completing the fundamentals classes would usually take 2 months.

After that, the duration would depend on the job track you choose. While you learn in a job track, you’ll build multiple real-world projects to build a digital portfolio that makes your profile super strong and attracts better salaries.

You’ll receive dedicated placement support for 16 months from the date of joining. During this period, you’ll prepare a strong resume with expert guidance and undergo mock interviews with your Tech and HR panels. This makes you ready to face the real interviews with confidence.

What is the NxtWave Intensive curriculum? Why is it so successful in getting tech jobs?

NxtWave Intensive Curriculum is proven to build industry-relevant skills. You can get ready for multiple IT career paths. You’ll start by learning fundamentals. After learning fundamentals, you can choose a job track.

• MERN Full Stack
• Java Full Stack
• Data Analytics
• QA / Automation Testing

You’ll also receive recommendation for a job track that is suitable for you based on your score in the fundamentals exam. Why is the NxtWave Intensive curriculum so successful? 

• The curriculum is designed by developers who built world-class products and worked at top technology companies like Amazon, Microsoft, etc. So it is industry aligned.
• You’ll learn by building real-world projects. For example, you'll learn by developing a web application like Zomato, Twitter etc. All these projects become your portfolio that you can showcase to anyone, anywhere.
• The curriculum is highly structured. You’ll learn everything in the perfect sequence. Also, the assessments, coding practices and mock tests help you build much-needed problem-solving and application skills.

Why is everyone saying NxtWave online sessions are extremely effective?

Well, on NxtWave platform, it’s not just online sessions. But it’s a comprehensive online learning ecosystem.

The platform is carefully designed to take care of all your learning needs. For example, 24x7 Online Labs allow you to practice instantly while you attend a session. And it’s natural to get doubts while you learn something new. But you’ll never get stuck up with them as you can reach out to domain experts without any hassles through discussion forums on the platform. 

By revising sessions anytime, you can learn in your own time and at your own pace. And you need not prepare summary notes for sessions because the summary notes/cheat sheets on the platform do that job for you with all the key concepts of a topic in one place.By revising sessions anytime, you can learn in your own time and at your own pace. And you need not prepare summary notes for sessions because the summary notes/cheat sheets on the platform do that job for you with all the key concepts of a topic in one place.

Most importantly, you’ll always stay motivated to learn because you can track your daily progress. And social learning with batchmates keeps you engaged.

Read many learners sharing their love and experience of learning online at NxtWave: https://www.ccbp.in/reviews

How flexible are the timings of NxtWave Intensive program?

You have the flexibility to learn at your convenient time and pace. However, we suggest you stick to a consistent time every day. Only when online live webinars happen, you need to attend them at a particular time. Mostly such webinars happen during weekends or in the evenings of working days. All the learning modules are very interactive.

What if I get doubts while learning?

Yes, it’s natural to get doubts while you learn something new. But you’ll never get stuck up with them as you can reach out to domain experts from 9 AM - 9 PM every day. Doubts clarification at NxtWave is 7x faster than the industry standards.

You can post your questions in the discussions forum and domain experts will get back to you with solutions/clarifications. You can also see the questions and answers posted by other students and get a better understanding of concepts.

Why is it recommended that you learn right from fundamentals at NxtWave Intensive?

In any skill, when you're strong with the fundamentals, you learn quickly and master it faster. And software development is no exception. If you build solid foundations, you can learn advanced concepts, languages and frameworks easily.

So we recommend that you learn right from fundamentals at NxtWave Intensive even though you have undergone training at another learning program. It is because you learn programming in a non-conventional way here that helps you develop the thinking patterns of a world-class developer.

How can I learn along with my college or office?

Learning in NxtWave Intensive is self-paced. You can join the program and learn after your working hours/regular college hours and on weekends.

Why anyone looking for a tech job can join the program without worrying about their background?

NxtWave Intensive program is designed to get anyone ready for a tech job within a short time. Your degree, branch, marks, or backlogs — nothing is a barrier to join the program and get a tech job. You need not have any previous coding knowledge. You’ll learn everything from scratch.

You may be afraid of coding due to your previous experiences. But our passionate trainers will simplify every concept and teach by integrating science-backed methods. So you'll understand concepts easily, and they stick in your mind instantly. It’s one of the reasons why many of your friends are recommending that you join NxtWave

Within 2-3 weeks at NxtWave, you’ll become amazingly confident about skills and fall in love with software development. By the time you complete the program, you’ll reach a stage where companies compete for you.

It’s because you’ll build industry-aligned real-time projects during the program and develop a strong personal portfolio. Due to this, your background (degree, marks, backlogs etc.) becomes insignificant before your skills.

Hundreds of NxtWave learners have proved that nothing matters to get a tech job except your willingness to learn. Read their inspiring success stories here: https://www.ccbp.in/reviews

The program is most suitable for final year students, job seekers, and those looking to switch to a tech career.

How are many graduates with B.A, B.Com, BSc, MBA degrees getting tech jobs?

Your degree is not a barrier to get a tech job. It’s because companies look for candidates with practical skills. As you’ll build many real-world projects during the program, your resume will become very powerful. And many NxtWave learners have proved it by getting placed on par with those who hold a Computer Science degree.

For example, Sonali, a NxtWave Intensive learner who has a specialization in Chemistry got a tech job with ₹7 Lakhs annual salary at ADF, a US-based tech company.

Similarly, there are many such successful career transformation stories. You can read them here: https://www.ccbp.in/reviews

Even with a career gap, how are many graduates getting a tech job?

Your career gap is not a barrier to getting a tech job. It’s because companies need candidates with practical skills. As you’ll build many real-world projects during the program, you’ll develop skills that companies look for. Similarly, there are many such successful career transformation stories of graduates with gaps in their resumes. You can read their reviews and success stories here: https://www.ccbp.in/reviews

So, you’ll have a good possibility to get hired by companies that are not concerned with your career gap. Though opportunities for candidates with career gaps are relatively lesser than recent graduates, there are many opportunities even then.

For example, Umamaheswari, a NxtWave Intensive learner graduated in 2015. Later, she got married and couldn't pursue a career as she took care of her family. She lost nearly 7 years. Now, Uma got placed as a Business Analyst at EXL Service.

You can read her journey here: https://www.linkedin.com/posts/uma-maheswari-v-_firstjob-success-professionalwomen-activity-6905457142984249344-ftqJ/

Similarly, there are many such successful career transformation stories of graduates with gaps in their resumes. You can read their reviews and success stories here: https://www.ccbp.in/reviews

How are many Non-CS branch (Mech, Civil, ECE, EEE, Chemical) graduates getting tech jobs?

Your branch is not a barrier to get a tech job. It’s because companies look for candidates with practical skills. As you’ll build many real-world projects during the program, your resume will become so powerful that your branch will become insignificant before your skills. And many NxtWave learners have proved it by getting placed on par with Computer Science students.

For example, Sushanth is a civil engineering graduate. After building programming skills from scratch at NxtWave, he cleared his first-ever tech interview in the first attempt itself. Now, he got placed as a Full Stack Developer at needl.ai with ₹11 lakhs per annum salary.

Similarly, hundreds of non-CS graduates got tech jobs with NxtWave Intensive. You can read their reviews and success stories here: https://www.ccbp.in/reviews

Do I need a laptop to attend NxtWave Intensive program?

Yes, you need a laptop to learn effectively. While you can attend sessions, participate in quizzes on your mobile, you will need a laptop to work on coding assignments and projects.

Recommended Specifications: ‍ Windows 10/Ubuntu/macOS 8GB RAM SSD

How long will I receive the placement support?

After you complete the job track you have chosen in the program, you’ll get Dedicated Placement Assistance with Aptitude Training, Communication Skills Training, Resume Preparation Support, Mock Interviews and more.

Then, you can sit for placements with companies that are hiring with NxtWave. During this period, interviews will be arranged until you get a job. There is no limitation on the number of interviews you can attend. 

You’ll receive dedicated placement support for 16 months from the date of joining. Please Note that NxtWave Intensive is NOT a Job Guarantee Program.

You can check out the success stories of students who placed through NxtWave here: https://www.ccbp.in/reviews

Do I have to take the first job I'm offered?

Yes. Once the placement process begins, we will recommend you to MNCs and startups in our network. You need to accept the job offer if you clear the selection process.

What is IRC?

IRC stands for Industry Ready Certification. Unlike any other study certificate, IRC represents your readiness for a job and approves that you have skills that companies look for.

NxtWave is one of the very few EdTech companies that is an Official Partner for NSDC, under the Ministry of Skill Development & Entrepreneurship, Govt. of India.

Your skills will be jointly certified by NxtWave and NSDC. It means you‘ll become more in demand for companies that hire NxtWave learners.

Why is IRC more powerful than regular study certificates?

It’s because an individual gets an IRC only after completing industry-aligned projects, assignments and tests designed by world-class developers in the NxtWave curriculum.

Governments/Govt. Organizations recognize only those training programs that are proven to bring results after an extensive evaluation process. So if you hold a certificate recognized by NSDC, companies have greater trust in your skills. It gives you an edge during interviews.

Is there an EMI option to pay the fee for NxtWave Intensive?

Yes, EMI support is available for credit cards. Please select the EMI option while making payment for more information.

What are the terms of the refund?

Please check the refund policy here: https://www.ccbp.in/terms-and-conditions#payments

Hear NxtWave learners' experiences

NxtWave in the media

Quick Links

• Hire with us
• 4.0 Champions
• NxtWave'22 Review
• Python Tutorial

Payment Methods

Course tracks.

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

SQL Exercises

• SQL Concepts and Queries
• SQL Inner Join
• SQL - SELECT LAST
• SQL for Data Science
• Comparison Operators in SQL
• SQL Query Interview Questions
• 7 Best Books for SQL
• SAP Labs Interview Experience
• Oracle Interview Experience
• Shell India Interview Experience
• DE Shaw Interview Experience
• TCS NQT Interview Experience
• Sapient Interview Experience | Set 4
• Spring Works Interview Experience
• TCS Ninja Interview Experience
• Infosys InfyTQ Interview Experience
• SAP Labs Interview Experience | Set 7

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 essential for our skills and language mastery.

In this article, we’ll cover a series of SQL practice exercises covering a wide range of topics suitable for beginners , intermediate , and advanced 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.

List of SQL Exercises

• SQL Questions for Practice

SQL Practice Exercises for Beginners

Sql practice exercises for intermediate, sql practice exercises for advanced, more questions for practice, sql exercises 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.

Explanation:

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 $500.

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.

By comparing the execution plans and analysis results of these queries, we can evaluate the performance implications of indexing the sale_date column. We’ll be able to observe differences in factors such as the query execution time, the type of scan used (sequential scan vs. index scan), and any additional costs associated with using the index.

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, improve your coding skills with practice.

What kind of Experience do you want to share?

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

sagarccbp/NodeJS-Assignment-1

Folders and files, repository files navigation, todo application.

Given an app.js file and database file todoApplication.db with a table todo .

Write APIs to perform operations on the table todo , with the following columns,

• Replace the spaces in URL with %20 .
• Possible values for priority are HIGH , MEDIUM , and LOW .
• Possible values for status are TO DO , IN PROGRESS , and DONE .
• Possible values for category are WORK , HOME , and LEARNING .
• The user may request with due date value as 2021-1-21 , format the date to 2021-01-21 and perform Create, Read, Update operations on the database.

Use date-fns format function to format the date. Refer to the documentation link for the usage of the format function.

Invalid scenarios for all APIs

Invalid Status

• Status code 400
• Body Invalid Todo Status

Invalid Priority

• Body Invalid Todo Priority

Invalid Category

• Body Invalid Todo Category

Invalid Due Date

• Body Invalid Due Date

Path: /todos/

Method: get.

Description :

Returns a list of all todos whose status is 'TO DO'

Returns a list of all todos whose priority is 'HIGH'

Returns a list of all todos whose priority is 'HIGH' and status is 'IN PROGRESS'

Returns a list of all todos whose todo contains 'Buy' text

Returns a list of all todos whose category is 'WORK' and status is 'DONE'

Returns a list of all todos whose category is 'HOME'

Returns a list of all todos whose category is 'LEARNING' and priority is 'HIGH'

Path: /todos/:todoId/

Description:.

Returns a specific todo based on the todo ID

Path: /agenda/

Returns a list of all todos with a specific due date in the query parameter /agenda/?date=2021-12-12

Method: POST

Create a todo in the todo table,

Method: PUT

Updates the details of a specific todo based on the todo ID

Method: DELETE

Deletes a todo from the todo table based on the todo ID

Use npm install to install the packages.

Export the express instance using the default export syntax.

Use Common JS module syntax.

• JavaScript 100.0%