use of aggregate function count in SQL

Let  be the number of CITY entries in STATION, and let  be the number of distinct CITY names in STATION; query the value of  from STATION. In other words, find the difference between the total number of CITY entries in the table and the number of distinct CITY entries in the table.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

query:

select count(city)- count( distinct city)

from station ;

Use of distinct keyword in SQL

Query a list of CITY names from STATION with even ID numbers only. You may print the results in any order, but must exclude duplicates from your answer.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

query:

select distinct city

from station

where MOD(id,2)=0;

sql select query 2

1)Query a list of CITY and STATE from the STATION table.

Input Format

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

query:

select city,state

from station

sql select statement

Query all columns for a city in CITY with the ID 1661.

Input Format

The CITY table is described as follows:

query:

select *

from CITY

where ID=1661;

Query for select statement

Query all columns for all American cities in CITY with populations larger than 100000. The CountryCode for America is USA.

Input Format

The CITY table is described as follows:

query:

select*

from CITY

where countrycode='USA' and population>100000;

Different Keys in SQL

Types of Keys

Primary Key:

The values in the column should Uniquely define the Record/Tuple in that Table.

ID

STUDENT_NAME

CLASS

MARKS

PHONE

Consider the above Table.Here the column ID can Uniquely Define each student in the system.Hence ID is the Primary Key.

Foreign Keys:

When the values in the column of the Referencing table contains a value of the Primary Key of the Reference Table, that column is known as Foreign Key of the table.

eg.

Consider that the Reference Table is:

Supplier_ID	Name	Phone
1	ABC	7846
2	DEF	7585
3	GHI	7886

And Referencing Table is:

Item_id	Item_name	Supplier
45	pencil	1
76	eraser	1
89	scale	2

Then the Supplier in the Referencing Table is the Foreign Key as it refers to the Supplier_ID in the Reference Table.

Note: Foreign Key can have repeated values as it refers to Primary Key of  reference table.

Candidate Key: It is defined as the minimal number of attributes required to identify a Record/Tuple.

eg.Consider the following table where the Primary key is Reg_no. and exam_seat_no.

Reg_no

Student_name

class

Exam_seat_no

DOB

Now, the Reg_no , Student_name and Eaxm_seat_no can uniquely define a student.

Similarly,  Reg_no and Exam_seat_no can uniquely define a student but it's not minimal .

Only  Reg_no or Exam_seat_No can Uniquely define a student hence Reg_no  and Exam_seat_no separately are Candidate keys.

Super Keys:

In above example, Reg_no and Student_name together or Reg_no and Exam_seat_no together or all the three together are called Super key.

So, Super Key  is basically the  group of columns  which can Uniquely define a Record/Tuple ,and not necessarily be minimal.

Note:

           EVERY CANDIDATE KEY IS A SUPER KEY  BUT A SUPER KEY MAY BE / MAY NOT             BE   A  CANDIDATE KEY.

Tutorials for mysql

I have created some videos for beginners trying to learn MySQL database.
Here are some videos which would help you to learn how to create tables, adding columns, modify columns, inserting data in  MySQL database.

Tutorial 1: Creating Tables In MySQL

Tutorial 2: Inserting Data in MySQL

Tutorial 3: Altering Table in MySQL

I'll keep uploading more videos so keep checking this post.