Question { 10356 }

what is primary key?

Answer

A primary key is used to uniquely identify each row in a
table. It can either be part of the actual record itself ,
or it can be an artificial field (one that has nothing to
do with the actual record). A primary key can consist of
one or more fields on a table. When multiple fields are
used as a primary key, they are called a composite key.
Primary keys can be specified either when the table is
created (using CREATE TABLE) or by changing the existing
table structure (using ALTER TABLE).
Below are examples for specifying a primary key when
creating a table:

MySQL:

CREATE TABLE Customer
(SID integer,
Last_Name varchar(30),
First_Name varchar(30),
PRIMARY KEY (SID));

Oracle:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));

SQL Server:

CREATE TABLE Customer
(SID integer PRIMARY KEY,
Last_Name varchar(30),
First_Name varchar(30));
Below are examples for specifying a primary key by altering
a table:

MySQL:

ALTER TABLE Customer ADD PRIMARY KEY (SID);

Oracle:

ALTER TABLE Customer ADD PRIMARY KEY (SID);

SQL Server:

ALTER TABLE Customer ADD PRIMARY KEY (SID);

Question { Infosys, 67511 }

Write a query to delete duplicate records in SQL SERVER

Answer

Delete From Tablename where(ID Not in (Select max(ID) from
Tablename Group by name))

Tablename :Friend

ID Name Age city
101 vinod 22 Gwalior
102 Pritesh 23 Gwalior
102 Pritesh 23 Gwalior
103 Arvind 24 Gwalior


Here Id-102 is repeated so friend if u want to delete this
duplicate raw Try Above code in Sql-sever

Question { Atiric Software, 8646 }

What is Partitions in Table ?

Answer

Partition can be considered as a piece of disk space, which
is marked thereby runs on some operating system. Partition
table is located at the first sector (cylinder 0, head 0
and sector 1, MBR) of each hard disk. It memorizes
information about sizes and locations of partitions on hard
disk. The partition information is started on offset 1BEH
of master boot sector. Each partition entry is 16 bytes
long. The total partition table is 64 bytes long. Then
partition table is limited to a maximum of 4 entries. That
is, there is a maximum of 4 partitions, which is called
primary partition and can be created on hard disk.

But there are problems: many people want to create more
than 4 partitions. So the extended partition is designed
for this demand. Master extended partition is the primary
partition. Differing from other partitions, the first
sector of extended partition is not a boot sector, but
another partition table, which is called logical partition
table.

Commonly, there are only two partition entries in logical
partition table. One points to a partition, called logical
partition, whose boundary must be limited to the extended
partition. The other entry, if needed, of the extended
partition table points to the next logical partition table.
Similarly, its boundary is limited to its parents extended
partition. And the next logical partition table may also
have two partition entries: one points to a logical
partition; the other points to another logical partition
table and the rest may be deduced by analogy. Therefore,
many partitions could be created in extended partition.

Now let's have a look at the layout of one partition entry.
The 16 bytes of one entry are as follows:

OFFSET BYTE DESCRIPTION
0 1 Boot label. Tell computer toboot from this partition
1 1 Starting head
2 1 Lower 6 bits (bit 0 to bit 5) isstarting sector.
Higher 2 bits (bit 6 to bit 7) is the higher bits of
starting cylinder
3 1 The lower 8 bits of starting cylinder

4 1 Partition type
5 1 Ending head
6 1 Lower 6 bits (bit 0 to bit 5) isending sector.
Higher 2 bits (bit 6 to bit 7) is the higher bits of ending
cylinder
7 1 The lower 8 bits of ending cylinder
8 4 Leading sectors of this partition
12 4 Number of sectors of this partition

(a) Boot label (offset 0):
Most of the disks have one primary partition. Some people
want to have more operating systems on their computers, so
they have to create some other primary partitions. To tell
the computer from which operating system to boot,
one "Active" partition is in need. That's why partition
table always keeps an indicator of the currently "Active"
partition - the one from which the computer boots. In
Partition Table Doctor or Super Fdisk, the active partition
is figured out by "Active" with "Yes".

(b) Starting position (offset 1-3):
Describes the partition's starting position, the cylinder,
the head and the sector. Also called starting CHS.
starting head = (OFFSET 1)
starting sector = (OFFSET 2) & 0x3f
starting cylinder = (((OFFSET 2) & 0xc0)<<2)|(OFFSET 3)

(c) Partition type (offset 4):
Indicates what file system is in the partition. For
example, 06 or 0E indicates a FAT file system. 0B or 0C
indicates a FAT32 file system. 07 indicates NTFS or OS/2
HPFS file system.

(d) Ending position (offset 5-7):
Describes the partition's ending position, the cylinder,
the head and the sector. Also called ending CHS.

(e) Leading sectors (offset 8-11):
The number of sectors before this partition. If we count
all sectors on hard disk in sequence from zero, this field
will exactly point to the first sector of this partition.

(f) Number of sectors (offset 12-15):
The total number of sectors on this partition. So the size
of this partition will be (Number of sectors)*512/1048576
MB.

Question { 11498 }

what is the difference between UNION AND UNIONALL

Answer

UNION
The UNION command is used to select related information
from two tables, much like the JOIN command. However, when
using the UNION command all selected columns need to be of
the same data type.

Note: With UNION, only distinct values are selected.

SQL Statement 1
UNION
SQL Statement 2

Employees_Norway:

E_ID E_Name
01 Hansen, Ola
02 Svendson, Tove
03 Svendson, Stephen
04 Pettersen, Kari

Employees_USA:

E_ID E_Name
01 Turner, Sally
02 Kent, Clark
03 Svendson, Stephen
04 Scott, Stephen


------------------------------------------------------------
--------------------

Using the UNION Command
Example
List all different employee names in Norway and USA:

SELECT E_Name FROM Employees_Norway
UNION
SELECT E_Name FROM Employees_USA

Result

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Scott, Stephen

Note: This command cannot be used to list all employees in
Norway and USA. In the example above we have two employees
with equal names, and only one of them is listed. The UNION
command only selects distinct values.


UNION ALL
The UNION ALL command is equal to the UNION command, except
that UNION ALL selects all values.

SQL Statement 1
UNION ALL
SQL Statement 2


------------------------------------------------------------
--------------------

Using the UNION ALL Command
Example
List all employees in Norway and USA:

SELECT E_Name FROM Employees_Norway
UNION ALL
SELECT E_Name FROM Employees_USA

Result

E_Name
Hansen, Ola
Svendson, Tove
Svendson, Stephen
Pettersen, Kari
Turner, Sally
Kent, Clark
Svendson, Stephen
Scott, Stephen