Archive
SQL : Paging in Stored Procedure
Create simple Student table using following script
CREATE TABLE [dbo].[Student]( [ID] [int] NOT NULL, [Name] [varchar](50) NOT NULL) ON [PRIMARY]GO
The OFFSET-FETCH clause is used to fetch specif records. OFFSET-FETCH can be used only with the ORDER BY clause.
Stored Procedure :
Limitations in Using OFFSET-FETCH
- ORDER BY is mandatory to use OFFSET and FETCH clause.
- OFFSET clause is mandatory with FETCH. You can never use, ORDER BY … FETCH.
- TOP cannot be combined with OFFSET and FETCH in the same query expression.
- The OFFSET/FETCH rowcount expression can be any arithmetic, constant, or parameter expression that will return an integer value. The rowcount expression does not support scalar sub-queries.
Function to find dates in current week in mysql
MySQL YEARWEEK() returns year and week number for a given date.
Syntax: YEARWEEK(date_value,Mode)
Parameters or Arguments
- date_value
- A date or datetime value from which to extract the year and week.
- mode
- Optional. It is used to specify what day the week starts on. It can be one of the following:
mode Explanation Week Value 0 First day of the week is Sunday 0-53 1 First day of the week is Monday and the first week has more than 3 days 0-53 2 First day of the week is Sunday 1-53 3 First day of the week is Monday and the first week has more than 3 days 1-53 4 First day of the week is Sunday and the first week has more than 3 days 0-53 5 First day of the week is Monday 0-53 6 First day of the week is Sunday and the first week has more than 3 days 1-53 7 First day of the week is Monday 1-53
Example : select YEARWEEK(now());
The following statement will return the year and week number of the date 2016-05-19.
SELECT YEARWEEK(‘2016-05-19’);
Hope this help!
SQL: Condition in Count()
Condition in count()
SELECT
count(case when EventTypeID =1 then 1 else null end) as TotalTournamentsAttended,
count(case when EventTypeID =2 then 1 else null end) as TotalLeaguesAttended
FROM
[Tables]
Difference Between Primary Key and Unique Key
| PRIMARY KEY | UNIQUE KEY | |
| NULL | It doesn’t allow Null values. Because of this we refer PRIMARY KEY = UNIQUE KEY + Not Null CONSTRAINT |
Allows Null value. But only one Null value. |
| INDEX | By default it adds a clustered index | By default it adds a UNIQUE non-clustered index |
| LIMIT | A table can have only one PRIMARY KEY Column[s] | A table can have more than one UNIQUE Key Column[s] |
| CREATE SYNTAX | Below is the sample example for defining a single column as a PRIMARY KEY column while creating a table:CREATE TABLE dbo.Customer ( Id INT NOT NULL PRIMARY KEY, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) )Below is the Sample example for defining multiple columns as PRIMARY KEY. It also shows how we can give name for the PRIMARY KEY: CREATE TABLE dbo.Customer |
Below is the sample example for defining a single column as a UNIQUE KEY column while creating a table:CREATE TABLE dbo.Customer ( Id INT NOT NULL UNIQUE, FirstName VARCHAR(100), LastName VARCHAR(100), City VARCHAR(50) )Below is the Sample example for defining multiple columns as UNIQUE KEY. It also shows how we can give name for the UNIQUE KEY: CREATE TABLE dbo.Customer |
| ALTER SYNTAX | Below is the Syntax for adding PRIMARY KEY CONSTRAINT on a column when the table is already created and doesn’t have any primary key:ALTER TABLE dbo.Customer ADD CONSTRAINT PK_CUSTOMER PRIMARY KEY (Id) |
Below is the Syntax for adding UNIQUE KEY CONSTRAINT on a column when the table is already created:ALTER TABLE dbo.Customer ADD CONSTRAINT UK_CUSTOMER UNIQUE(Id) |
| DROP SYNTAX | Below is the Syntax for dropping a PRIMARY KEY:ALTER TABLE dbo.Customer DROP CONSTRAINT PK_CUSTOMER |
Below is the Syntax for dropping a UNIQUE KEY:ALTER TABLE dbo.Customer DROP CONSTRAINT UK_CUSTOMER |
MySQL : coalesce in mysql
DESCRIPTION
The MySQL COALESCE function returns the first non-null expression in the list.
SYNTAX
The syntax for the MySQL COALESCE function is:
COALESCE( expression1, expression2, ... expression_n ) e.g. SELECT COALESCE(t.col,1) FROM Table t Above query will return 1 if t.col is null. Hope this help !
Full form
WSDL : Web Services Description Language
REST : Representational State Transfer
SOAP : Simple Object Access Protocol
..Will add some more soon
Comma separated values in mysql
MY SQL:
GROUP_CONCAT function concatenates values within each group defined by GROUP BY clause.
| Syntax | GROUP_CONCAT([DISTINCT] exp [ORDER BY sorting] [SEPARATOR ‘sep’]) |
| Quick Example | SELECT GROUP_CONCAT(city) FROM cities GROUP BY state; |
| Separator | Comma (,) by default, ” eliminates separator |
| NULL Values | Skipped |
| Max Length | 1024 by default, specified by group_concat_max_len system variable |
Sample:
CREATE TABLE cities ( city VARCHAR(70), state CHAR(2) ); INSERT INTO cities VALUES ('San Francisco', 'CA'); INSERT INTO cities VALUES ('San Diego', 'CA'); INSERT INTO cities VALUES ('Los Angeles', 'CA'); INSERT INTO cities VALUES ('Austin', 'TX'); INSERT INTO cities VALUES ('Houston', 'TX');
SELECT state, GROUP_CONCAT(city) FROM cities GROUP BY state; The result:
| CA | San Francisco,San Diego,Los Angeles |
| TX | Austin,Houston |
SQL Server:
SELECT state, STUFF((SELECT ',' + city FROM cities WHERE state = c.state FOR XML PATH('')),1 ,1 ,'') FROM cities c GROUP BY state Hope this help ! Rahul Bhatia
Rahul Bhatia
Comments