database assignment answers

SQL Tutorial

Sql database, sql references, sql examples, sql exercises.

You can test your SQL skills with W3Schools' Exercises.

We have gathered a variety of SQL exercises (with answers) for each SQL Chapter.

Try to solve an exercise by filling in the missing parts of a code. If you're stuck, hit the "Show Answer" button to see what you've done wrong.

Count Your Score

You will get 1 point for each correct answer. Your score and total score will always be displayed.

Start SQL Exercises

Start SQL Exercises ❯

If you don't know SQL, we suggest that you read our SQL Tutorial from scratch.

Kickstart your career

Get certified by completing the course

COLOR PICKER

Report Error

If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:

[email protected]

Top Tutorials

Top references, top examples, get certified.

• SQL Server training
• Write for us!

SQL Practice – common questions and answers for SQL skills

This article will show some important questions and answers to practice SQL.

Introduction

SQL is the base to handle different databases. Even some of the NoSQL databases use an extension of SQL to query data. Data Scientists, BI Analysts, BI Developers, DBAs, Database Developers, Data Engineers, Reporting Analysts, and several other jobs require SQL knowledge as part of the knowledge required. For data management, SQL knowledge is the base to handle databases and the numbers of jobs related to databases are growing each day. The software industry, including databases, is the world’s most in-demand profession. That is why in this article, we will show some questions and answers to practice SQL.

Requirements

In order to start, you need the SQL Server installed and the Adventureworks sample database installed. The following links can help you if you do not have them installed:

• How to install SQL Server developer edition
• Install and configure the AdventureWorks2016 sample database

Q1. Please select all the information of persons table that the name starts with A

A1. You will show all the columns with the select * and the where [FirstName] like ‘a%’ will filter the data to all the persons that the name starts with a. The query used is the following:

The LIKE operator is very common in SQL queries. The like ‘a%’ will show all the first names that start with the letter a. There are several other operators that you need to practice like the EXISTS, IN, =, <>, ANY. The following link provides more information about the operators:

• Operators (Transact-SQL)

Note that the use of * is a bad practice for big tables, but in this case, it is a small table, so it will not impact performance. In general, try to select only the columns required and not all.

Q2. Create a store procedure that receives the first name of the person table as input and the last name as output.

In SQL practice, it is necessary to mention the stored procedures because they are frequently used in SQL Server. The stored procedures are T-SQL sentences that cannot be applied to other databases like Oracle, MySQL, PostgreSQL. The following sample of code shows how to create a stored procedure named GetLastName. It receives the parameter @firstname and returns the last name of the provided firstname. It queries the Person table:

To call the stored procedure using T-SQL, you will need to declare a variable to store the output variable. We use the execute command to call a stored procedure. John is the parameter value for the first name and the last name is the output of the stored procedure:

The result displayed by this store procedure invocation will be something like this:

For more information about creating stored procedures, please refer to this link:

• Learn SQL: User-Defined Stored Procedures
• SQL Server stored procedures for beginners
• CREATE PROCEDURE (Transact-SQL)

Q3. Which query would you execute to delete all the rows from the person table with minimal bulk-logged activity?

A3. The truncate table sentence removes data without logging individual row deletions. It is the most efficient way to remove all the data. The delete command, on the other hand, logs a lot of data if we have multiple rows and can consume a lot of space in the transaction log files. So, the code will be the following:

Truncate table person.person

To practice SQL, try the truncate and delete statements. For more detailed information about the differences between the truncate and the delete sentences, refer to this link:

• The internals of SQL Truncate and SQL Delete statements

Q4. Create a query to show the account number and customerid from the customer table for the customer without sales orders.

A4. The query would be something like this:

The result displayed will be something like this:

We are using the left join to look for all the customers without sales, so the salesorderid will be null. In SQL practice, you need to know the use of the different JOINS like the LEFT JOIN, RIGHT JOIN, CROSS JOIN. We created some articles about the different types of JOINs here. It is very important for you to check, practice, and study all the options:

• Learn SQL: Join multiple tables
• Learn SQL: INNER JOIN vs LEFT JOIN
• SQL CROSS JOIN with examples

We could also use the EXISTS or the IN operators instead of using the JOINS. The performance is different according to the scenario. The performance in queries is out of the scope of this SQL practice. However, we have an entire article about this topic here:

• How to design SQL queries with better performance: SELECT * and EXISTS vs IN vs JOINs

Q5. You have a table with some of the temperatures in Celsius of some patients. Create a function or a stored procedure to get the convert Fahrenheit to Celsius. Would you use a function or a stored procedure?

A5. Basically, the question here is to create a stored procedure or a function to convert from Celsius to Fahrenheit. If you already have a table with some rows in Celsius, the easiest option is to use a function. The function could be something like this:

Invoking a function is easier than a stored procedure. For more information about the use of functions vs stored procedures, refer to this link:

• Functions vs stored procedures in SQL Server

Q6. Create a query to show the top 10 customerIDs of users with more Orders.

A6. For this practice test, we will use the TOP 10 to get the customer IDs with more orders. We will use the SUM to SUM the Order Quantity column. Note that for aggregated functions like the SUM, the alias is needed to define the column name in the query. Also, the Sum is grouped by the customer ID. Usually aggregated functions come with the GROUP BY clause. Finally, we are using the order by to order the result in descendant order:

For more information about aggregated functions like the SUM, MAX, MIN, AVG, refer to this link:

• Aggregate Functions (Transact-SQL)

In this article, we show different questions for SQL practice. We show some questions and answers to practice SQL and improve the knowledge. If you have more questions about this topic, do not hesitate to contact us.

• Recent Posts

• PostgreSQL tutorial to create a user - November 12, 2023
• PostgreSQL Tutorial for beginners - April 6, 2023
• PSQL stored procedures overview and examples - February 14, 2023

Related posts:

• A complete guide to T-SQL Metadata Functions in SQL Server
• Top SQL Server Books
• An overview of the SQL Server Update Join
• FOR XML PATH clause in SQL Server
• Creating and using CRUD stored procedures

[Week 1] NPTEL Introduction to Database Systems Assignment Answers 2024

Nptel introduction to database systems week 1 assignment answers 2024.

1. Storing data and meta-data separately makes RDBMS general-purpose and flexible.

2. “Data Model” is nothing but a data structure where data is stored.

3. An RDBMS can typically manage more than one database.

4. A database can not be created unless an RDBMS is used.

5. Relational data model provides the concept of “Entity”.

6. ER Model is NOT a representational-level data model.

7. Three-schema architecture is part of the Entity-Relationship Data Model.

8. Transaction Manager takes control of the system when it recovers after a failure.

9. People who make use of application programs need to know the logical schema of the database completely.

10. People who develop the application programs need not know the physical schema of the database.

11. People who develop the application programs need to know the logical schema of the database completely.

12. A “log” in the RDBMS keeps track of update operations of all transactions.

13. Complete the sentence: Logical Data Independence is the ability to modify…

• physical-level schema without affecting the logical-level schema
• the logical-level schema with no effect on view-level schema.
• view-level schema without affecting logical-level schema.
• logical-level schema without affecting physical-level schema.

14. Complete the sentence: Physical Data Independence is the ability to modify…

15. An Entity-Relationship (ER) Model represents:

• The various entity types of interest and the relationships among them in the domain being modeled.
• Various tables and links among them in the domain being modeled.
• The various entity types of interest and the relationships among them in the domain being modeled along with operations to be performed on data.
• Various tables and links among them in the domain being modeled along with operations to be performed on data.

16. A person who develops a high-level language program that meets a functional requirement of the database is usually called:

• A naive user
• An application programmer
• A data analyst
• A DB administrator

17. The people playing the following role need​  NOT ​ have an understanding of the complete logical schema of the database:

• Data-entry Operator
• Application Programmer
• Data Analyst
• Database Administrator

Consider the following statements:

   S1: A view usually provides access to a part of the data relevant to a group of users    S2: A view usually provides access to the complete database    S3: The feature of defining views is to be compulsorily provided by the RDBMS    S4: The feature of defining views may not be provided by an RDBMS.

   Choose the correct option:

• S1 and S3 are TRUE
• S1 and S4 are TRUE
• S2 and S3 are TRUE
• S2 and S4 are TRUE

Consider the following sets:

   M = { p: RDBMS Run-time System; q: Transaction Manager; r: Buffer Manager;

    s: Recovery Manager}

   F = { w: Transaction Error Handling; x: Concurrency Control; y: Query Execution; z: Paging}

   What is the most appropriate matching between sets M and F?

• p — y; q — x; r — z; s — w;
• p — z; q — w; r — y; s — x;
• p — y; q — z; r — x; s — w;
• p — w; q — x; r — z; s — y;

Share your love

Related posts.

[Week 1-11] NPTEL Programming In Java Assignment Answers 2023

[week 1] nptel programming in modern c++ assignment answers 2024, [week 1] nptel soft skill development assignment answers 2024, [week 1] nptel software testing assignment answers 2024.

NPTEL Stress Management Assignment Answer (week 1-2) 2023

[Week 1] NPTEL Business Fundamentals For Entrepreneurs Assignment Answers 2023

Leave a comment cancel reply.

Your email address will not be published. Required fields are marked *

Save my name, email, and website in this browser for the next time I comment.

If You Are Facing Any Problem In Payment Then Email On : [email protected]

[week 1-12] nptel introduction to database systems assignment answers 2024.

About Course

This course will provide you with access to all 12 weeks of assignment answers for NPTEL Introduction to Database Systems . As of now, we have uploaded the answers of Week 10.

Note:- Buy this plan if you have not yet. Our answers will be visible to only those who buy this plan.

Course Content

Week 1 answers, week 1 assignment answers, week 2 answers, week 2 assignment answers 2024, week 3 answers, week 3 assignment answers 2024, week 4 answers, week 4 assignment answers 2024, week 5 answers, week 5 assignment answers 2024, week 6 answers, week 6 assignment answers 2024, week 7 answers, week 7 assignment answers 2024, week 8 answers, week 8 assignment answers 2024, week 9 answers, week 9 assignment answers 2024, week 10 answers, week 10 assignment answers 2024, week 11 answers, week 11 assignment answers 2024, student ratings & reviews.

Want to receive push notifications for all major on-site activities?

Insert/edit link

Enter the destination URL

Or link to existing content

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

Infosys DBMS Course Exercises and Assignment solutions

Aashrith001/INFYTQ-DBMS

Folders and files, repository files navigation.

INFYTQ-DBMS Infosys DBMS(Part-1 & Part-2) Course Exercises and Assignment Solutions

Database Management System Part-1 https://infytq.onwingspan.com/en/app/toc/lex_auth_01275806667282022456_shared/overview

Database Management System Part-2 https://infytq.onwingspan.com/en/app/toc/lex_auth_0127673005629194241_shared/overview