Go on. Explore!

lvl 0 • 0 xp

lvl 1

Home Market Decks Interests

SQL

memorize.ai (lvl 286)

(0)

14 cards

()

Section 1

Preview this deck

What is SQL?

Front

1 / 14

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 (14)

Section 1

(14 cards)

What is SQL?

Front

Structured Query Language. It is a language to interact with the database. ie. create database, create table in the database, retrieve and update data in the database.

Back

What is the difference between primary key and unique constraints?

Front

Primary key cannot have NULL value, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constrains. The primary key creates the cluster index automatically but the Unique key does not.

Back

What is a Database?

Front

A Database is defined as a structured form of data which is stored in a computer or in cloud. It is also the collection of schemas, tables, queries, views etc. Database helps us in easily storing, accessing and manipulation of data The Database Management System allows a user to interact with the database.

Back

Name different types of case manipulation functions available in SQL.

Front

There are three types of case manipulation functions available in SQL. They are, LOWER: The purpose of this function is to return the string in lowercase. It takes a string as argument and returns the string by converting it into lower case. Syntax: LOWER('string') UPPER:The purpose of this function is to return the string in uppercase. It takes a string as argument and returns the string by converting it into uppercase. Syntax: UPPER('string') INITCAP:The purpose of this function is to return the string with first letter in uppercase and rest of the letters in lowercase. Syntax: INITCAP('string')

Back

What is a view in SQL?

Front

Views in SQL are kind of virtual tables. the data from a view is not stored physically. It is a set of queries that, when applied to one or more tables, is stored in the database as an object. Now suppose that the Administrator do not want that the users to access the whole data of Emp_Details table which contains some critical information (Emplogin, EmpPassword, etc.) of the Employees. So he can create a view which gives the empid, empname, employmentdate as the output and gives permission for the view to the user. In this way, the administrator does not need to give access permission for the table to the user. Views are used for security purposes because they provide encapsulation of the name of the table. Data is in the virtual table, not stored permanently. Views display only selected data.

Back

How to optimize the SQL query

Front

1. index properly 2. Only Retrieve the Data You Really Need: specifying the columns might save a lot of computation time instead of using * astroid sign: limited number of rows you should use the LIMIT 3. Avoid Functions on the Left Hand-Side of the Operator: 4. Consider Getting Rid of Correlated Subqueries: If there are millions of users in the database, the statement with the correlated subquery will most likely be less efficient than the INNER JOIN because it needs to run millions of times. 5. Avoid Wildcard Characters at the Beginning of a LIKE Pattern: Whenever possible, avoid using the LIKE pattern in the following way: SELECT * FROM users WHERE name LIKE '%bar%';

Back

What do you mean by foreign key?

Front

A Foreign key is a field which can uniquely identify each row in another table. That is, this field points to primary key of another table. This usually creates a kind of link between the two tables.

Back

What is a join in SQL? What are the types of joins?

Front

An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are: INNER JOIN: LEFT JOIN: RIGHT JOIN: FULL JOIN:

Back

Does SQL support programming language features ?

Front

It is true that SQL is a language but it does not support programming as it is not a programming language, it is a command language. We do not have conditional statements in SQL like for loops or if..else, we only have commands which we can use to query, update , delete etc. data in the database. SQL allows us to manipulate data in a database.

Back

LIKE operator

Front

SELECT * FROM Employees WHERE EmpName like 'A%' ;

Back

DDL: Data Definition Language DML: Data Manipulation Language

Front

The commands to create, modify, or delete tables, columns & databases. Examples: CREATE, ALTER, DROP DML is a subset of SQL that involves querying and manipulating records in existing tables. Most of the DML you'll be doing will be related to CRUD operations on rows. The commands to create, read, modify, or delete rows. Examples: SELECT, INSERT, UPDATE, DELETE`

Back

What is the difference between BETWEEN and IN operators in SQL?

Front

The BETWEEN operator is used to fetch rows based on a range of values. For example, SELECT * FROM Students WHERE ROLL_NO BETWEEN 20 AND 30; This query will select all those rows from the table Students where the value of the field ROLL_NO lies between 20 and 30. IN The IN operator is used to check for values contained in specific sets. For example, SELECT * FROM Students WHERE ROLL_NO IN (20,21,23); This query will select all those rows from the table Students where the value of the field ROLL_NO is either 20 or 21 or 23.

Back

What is the difference between CHAR and VARCHAR2 datatype in SQL?

Front

Both of these datatypes are used for characters but varchar2 is used for character strings of variable length whereas char is used for character strings of fixed length. CHAR type has fixed size, so if you say it is 10 bytes, then it always stores 10 bytes in the database and it doesn't matter whether you store any text or just empty 10 bytes VARCHAR2 size depends on how many bytes you are actually going to store in the database. The number you specify is just the maximum number of bytes that can be stored (although 1 byte is minimum)

Back

index for database

Front

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data.

Back