This week, I learnt some more things about databases and SQL Server. The learnings of this week are:
- CONSTRAINTS: These describe the limitations on the data in the table and hence, ensures the reliability of the data. For example, we can specify that a column doesn’t have a null value. If there will be any violation, the action will be aborted. In a similar way, we can set a default value or null value to the table.
- PRIMARY KEY: It is that special feature of a table which uniquely identifies each record in the database table. It represents the unique value of table and thus, prevents the duplicity of data in the table. A primary key with more than one field is called composite key. For instance, in a table of employees of an organisation, the employee ID would be the primary key. It can never have a null value.
The primary key of one table is called Foreign key in another table.
- SELECT: SELECT statement is used to fetch data, in other words, make queries from a database table.
Following is the syntax used for a select statement:
SELECT column1, column2, columnN FROM table_name;
The SELECT statement is usually followed by various clauses to limit the query. these clauses are WHERE(to add specific condition), DISTINCT(to avoid duplicate values), FROM(to mention an address of the table from the database), LIKE(to specify a pattern in a column and is oftenly used with wildcard characters like %).
- WHERE CLAUSE: As mentioned above it is used to add a particular condition. But it is not limited to SELECT statement. It is also used with DELETE, JOINS and UPDATE statements. We can limit our result using it. For example, in the table of employees, if want to search for workers in any particular department say marketing we can type:
WHERE [department= marketing ]
It will give the details of all the employees in the marketing department.
- JOINS: This is used to create a new result set of a table after combining the rows from two or more tables. There are different types of join statements.
Among these, CROSS JOIN is least useful as it produces the result table with the method known as the cartesian product. If we are joining two tables which have 100 rows each, the result table will have 10000 rows. Hence we can conclude it results into duplicity of data.
The most commonly used JOIN statement is INNER JOIN. It creates a new result table by combining column values of two tables after comparing each row of table1 with each row of table2 to find all pairs of rows which satisfy the join predicate. It is followed by ON clause, which is used for mentioning the common fields(join predicates).
- INSERT and UPDATE: These are used to insert a new row and update an existing data in the table.
INSERT INTO TABLE_NAME (column1, column2, column3,...columnN)
VALUES (value1, value2, value3,...valueN);
SET column1 = value1, column2 = value2...., columnN = valueN
- COUNT: This command gives the count of rows in the column specified in the query.