Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

16 cards

()

Section 1

Preview this deck

What is the difference between a UNION and a JOIN?

Front

1 / 16

SQL

0.0

0 reviews

5			0
4			0
3			0
2			0
1			0

Active users

All-time users

Favorites

Last updated

6 years ago

Date created

Mar 1, 2020

Cards (16)

Section 1

(16 cards)

What is the difference between a UNION and a JOIN?

Front

A JOIN selects columns from 2 or more tables. A UNION selects rows.

Back

Difference between Primary key and Unique key.

Front

Primary key is a key that is chosen by the database designer as the principal identifier for tuples within a relation. both the primary Key and Unique Key enforce uniqueness of the attribute within a table. but the Primary Key does not allow null values where Unique Key allow it.

Back

What is normalization? Explain different levels of normalization?

Front

a way to eliminate redundant data Reduces dependency Reduces null value Enables efficient indexing 1NF - Removes duplicated attributes. atomic values only 2NF - Should be in 1NF, and each non-key is fully dependent on the primary key. 3NF - Should be in 2NF and all the non-key attributes which are not dependent on the primary key should be removed. All the attributes which are dependent on the other non-key attributes should also be removed.

Back

SUM

Front

Back

What is de-normalization and when would you go for it?

Front

It is the reverse process of normalization. It increases the query performance by reducing the joins.

Back

Define following keys: Candidate key, Alternate key, Composite key.

Front

Candidate key -Key which can uniquely identify a row in table. Alternate key - when one Candidate key becomes a primary key the rest becomes alternate keys. Composite key -More than one key uniquely identify a row in a table.

Back

when to use HAVING

Front

Back

ORDER BY

Front

Back

How do you implement one-to-one, one-to-many and many-to-many relationships while designing tables?

Front

One to One -It can be implemented as a single table. Rarely it is implemented in two tables. For each instance in the first entity there is one and only one in the second entity and vice versa. One to Many -For each instance in the first entity there can be one or more in the second entity. For each instance in the second entity there can be one and only one instance in the first entity. Many to Many -For each instance in the first entity there can be one or more instance in the second entity and moreover for each instance in the second entity there can be one or more instance in the first entity.

Back

What is a Join and explain different types of Joins?

Front

is used to combine rows from two or more tables, based on a related column between them. SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID; INNER JOINs, OUTER JOINs, CROSS JOINs. OUTER JOINs are further classified as LEFT OUTER JOINS, RIGHT OUTER JOINS and FULL OUTER JOINS.

Back

What are the main steps in Data Modeling?

Front

Logical - Planning, Analysis and Design Physical - Design, Implementation and Maintenance

Back

Write down the general syntax for a SELECT statement covering all the options.

Front

SELECT select_list FROM table_source WHERE search_condition GROUP BY group_by_expression HAVING search_condition ORDER BY order_expression [ASC | DESC]

Back

Group By

Front

Back

What is the system function to get the current user's user id?

Front

USER_ID()

Back

What can a developer do during the logical and physical design of a database in order to help ensure that their database and SQL Server-based application will perform well?

Front

A developer must investigate volumes of data (capacity planning), what types of information will be stored, and how that data will be accessed. If you are dealing with an upgrade to an existing system, analyzing the present data and where existing data volumes occur, how that data is accessed and where the current response bottlenecks are occurring, can help you search for problem areas in the design. A new system would require a thorough investigation of what data will be captured, and looking at volumes of data held in other formats also will aid design. Knowing your data is just as important as knowing the constituents of your data. Also, constantly revisit your design. As your system is built, check relationships, volumes of data, and indexes to ensure that the physical design is still at its optimum. Always be ready to check your system by using tools like the SQL Server Profiler.

Back

find the sum of names that are added each day from a table that have names and added_dates in it.

Front

Back