رفرنس- لینک
1- RDBMS چیست؟
RDBMS داده ها را در مجموعه ای از جدول ها ذخیره میکنند که بین آنها ارتباط وجود دارد.
RDBMS store data into a collection of tables, which is related
by common fields between the columns of the table. It also
provides relational operators to manipulate the data stored
into the tables.
Example: SQL Server.
2- SQL چیست؟
زبان ساختار یافته برای ارسال درخواست که برای ارتباط با دیتابیس مناسب است. یک زبان استاندارد است که برای بازیابی یا retrieval و به روز رسانی یا update و ایجاد یا insertion یا حذف یا delete داده از دیتابیس مناسب است
SQL stands for Structured Query Language , and it is used to
communicate with the Database. This is a standard
language used to perform tasks such as retrieval, updates,
insertion and deletion of data from a database.
Standard SQL Commands are Select.
3- دیتا بیس چیست؟
مجموعه ساختار یافته از دیتا. برای مثال دیتابیس مدیریت مدرسه یا دیتابیس اطلاعات بانک و …
A Database is an organized form of data for easy access,
storing, retrieval and managing of data. This is also known
as structured form of data which can be accessed in many
ways.
Example: School Management Database, Bank Management
Database.
4- primary key چیست؟
مجموعه ای از فیلدها (منظور شاخص یا index است) که به صورت یکتا هر ردیف را نشان میدهد. یک مشخصه الزامی آن این است که نباید null باشد.
A primary key is a combination of fields which uniquely
specify a row. This is a special kind of unique key, and it has
implicit NOT NULL constraint. This means, Primary key values
cannot be NULL.
5- unique key چیست؟
A Unique key constraint uniquely identifies each record in a
database. This provides uniqueness for the column or set of
columns. A Primary key constraint has automatic unique
constraint defined on it.
تفاوت آن با primary key چیست؟
There can be many unique
constraints defined per table, but only one Primary key
constraint defined per table.
6- foreign key چیست؟
یک جدول است که میتواند بعنوان primary key برای جدول دیگر باشد.
A foreign key is one table which can be related to the
primary key of another table. Relationships need to be
created between two tables by referencing the foreign key
with the primary key of another table
7- تفاوت بین spreadsheets و databases در چیست؟
spreadsheets
A file that exists of cells in rows and columns and can help
arrange, calculate and sort data.
It can have numeric
values, text, formulas and functions. It features columns and
rows to keep inserted information legible and simple to
understand. It is an electronic graph sheet
databases.
It is an organized collection of data arranged for ease and
speed of search and retrieval. It contains multiple tables. A
database engine can sort, change or serve the information
on the database. Basically, it is a set of information which is
held in a computer
8-What are table and fields
A table is a set of data that are organized in a model with
Columns and Rows. Columns can be categorized as vertical,
and Rows are horizontal.
تفاوت field و record
A table has a specified number of
column called fields but can have any number of rows
which are called records.
Example: مثال
Table: Employee.
Field: Emp ID, Emp Name, Date of Birth.
Data: 201456, David, 11/15/1960.
9- انواع زبان SQL
DDL
DDL changes the structure of the table like creating a table,
deleting a table, altering a table, etc. All the commands of
DDL are auto-committed which means that it permanently
saves all the changes in the database.
Some commands that come under DDL:
CREATE; ALTER; DROP; TRUNCATE
DML
DML commands are used to modify the database. It is
responsible for all forms of changes in the database. The
commands of DML are not auto-committed which means
that it can’t permanently save all the changes in the
database.
Some commands that come under DML:
INSERT; UPDATE; DELETE
DCL = data control language
DCL commands are used to grant and take back authority
from any database user.
Some commands that come under DCL:
Grant; Revoke
TCL=transaction control language
TCL commands can only be used with DML commands like
INSERT, DELETE and UPDATE. These operations are
automatically committed in the database, which is why they
cannot be used while creating tables or dropping them.
Some commands that come under TCL:
COMMIT; ROLLBACK; SAVEPOINT
DQL=data query language
DQL is used to fetch the data from the database.
It uses only one command:
SELECT
10-What is normalization
فرآیند کاهش ناهمواری در داده را گویند
Normalization is the process of minimizing redundancy and
dependency by organizing fields and tables of a database.
The main aim of Normalization is to add, delete or modify
fields that can be made in a single table
11- What is denormalization
اگر بخواهیم دیتای اصلی را بازیابی کنیم از این روش استفاده میکنیم.
Denormalization is a technique used to access the data
from higher to lower normal forms of database. It is also a
process of introducing redundancy into a table by
incorporating data from the related tables.
12- Explain the different types of normalization.
نوع اول: حذف تمامی مقادیریا ستونهای دابل
First Normal Form (1NF): This should remove all
the duplicate columns from the table. Creation
of tables for the related data and identification
of unique columns.
نوع دوم: اجرای تمام الزامات نوع اول+ …
Second Normal Form (2NF): Meeting all
requirements of the first normal form. Placing the
subsets of data in separate tables and Creation
of relationships between the tables using primary
keys.
نوع سوم:اجرای تمام الزامات نوع دوم + …
Third Normal Form (3NF): This should meet all
requirements of 2NF. Removing the columns
which are not dependent on primary key
constraints.
نوع چهارم: اجرای تمام الزامات نوع سوم + …
Fourth Normal Form (4NF): Meeting all the
requirements of third normal form and it should
not have multi- valued dependencies.
13-What are views in SQL
یک جدول مجازی که …
A view is a virtual table which consists of a subset
of data contained in a table. Views are not virtually
present, and it takes less space to store. View can
have data of one or more tables combined, and it is
depending on the relationship.
14-What is join? Explain the different types.
زمانی که بخواهیم کوئری های ترکیبی بزنیم.
This is a keyword used to query data from more tables
based on the relationship between the fields of the
tables. Keys play a major role when JOINs are used.
There are various types of joins which can be used to
retrieve data and it depends on the relationship between
tables.
Left Outer Join: If we want all the records from left
table and only matching records from right table then
will use left outer join/left join.
Right Outer Join: If we want to display all the records
from right table and only matching records from left
table then will right outer join/right join
Full Outer Join: If we want to display all the records
from both the tables then will use full outer join
Inner Join: If we want only the matching records from
both the tables then will use Inner join/Simple join
15- What are the different types of indexes?
An index is a performance tuning method of allowing
faster retrieval of records from the table. An index creates
an entry for each value and makes it faster to retrieve
data
3 نوع ایندکس داریم:
Unique Index: This indexing does not allow the field to
have duplicate values if the column is unique indexed.
Unique index can be applied automatically when
primary key is defined
Clustered Index: This type of index reorders the
physical order of the table and search based on the
key values. Each table can have only one clustered
index
Non-Clustered Index: Non-Clustered Index does not
alter the physical order of the table and maintains
logical order of data. Each table can have 999 nonclustered indexes
16-What is a cursor in SQL?
A database Cursor is a control which enables traversal
over the rows or records in the table. This can be viewed
as a pointer to one row in a set of rows. Cursor is very
much useful for traversing such as for retrieval, addition
and removal of database records
17-What is query?
A DB query is a code written in order to get the
information back from the database. Queries can be
designed in such a way that it matches with our
expectation of the result set.
18-What is a subquery?
A subquery is a query within another query. The outer
query is called as main query, and inner query is called
subquery. SubQuery is always executed first, and the
result of subquery is passed on to the main query.
دو نوع سابکوئری داریم:
There are two types of subquery – Correlated and NonCorrelated.
A correlated subquery cannot be considered as an
independent query, whereas a Non-Correlated sub query
can be considered as independent query and the output
of subquery are substituted in the main query.
19-What is a trigger?
A DB trigger is a code or programs that
automatically execute with response to some event
on a table or view in a database. Mainly, trigger
helps to maintain the integrity of the database.
مثال : زمانی که یک دانش آموز به جدول اضافه شود…
Example: When a new student is added to the
student database, new records should be created in
the related tables such as the Exam, Score and
Attendance tables
20-Differentiate between the DELETE and TRUNCATE commands.
DELETE command is used to remove rows from
the table, and WHERE clause can be used for
conditional set of parameters. Commit and
Rollback can be performed after delete
statement
TRUNCATE removes all rows from the table.
Truncate operation cannot be rolled back