SQL

  1. Primary key: A PRIMARY KEY constraint is one which uniquely identifies each record in a table and cannot contain NULL values (mySQL). A table can have only one primary key. On the other hand, unique can contain NULL value (even multiple).
Questions:
  1. Find second highest salary.
  2. Find employees those who have joined in last month.
    1. NOW()
    2. MONTH(column_name)
  3. How to write foreign key relation. (Inner/left join and start with child table)
  4. Difference between primary key and foreign key
  5. Composite key.
  6. Indexing
  7. sql freeze vs lock

Indexing

Indexing a column can speed up the searches (i.e. select * from where id=123). Indexing is helpful for searches but can hamper the speed during create, update, delete operations. Because,
1. For clustered index, column is reordered after inserting.
2. For non-clustered index, index value (like a new field of db) is needed to create.

During the search operation the Binary search operation is used instead of default Linear search operation. In binary search time get reduced.
Reference:

Unicode/UTF-8 data in mysql table

Set collation of table to utf8_unicode_ci

How to Count the String Occurrences in SQL

The following query will find the number of occurrences of 'is' in in Notes row of Employees table
SELECT Notes, (LENGTH(Notes)-LENGTH(REPLACE(Notes, 'is', '')))/LENGTH('is') AS No_of_is FROM Employees;

 You can test the query in the link below:

https://www.w3schools.com/sql/trysql.asp?filename=trysql_select_having2


Labels: ,

Edit This post
© copyright-2020 Rejaul