> Code Storage: mysql

Code Storage

A Technical blog for those who want to gain knowledge about SQL,MySQL,Data Science and real time query

Responsive Ads Here

Advertisement

Showing posts with label mysql. Show all posts
Showing posts with label mysql. Show all posts

Friday, January 17, 2020

Date and Time format in SQL ?

date , Date and time formate
Date and Time format in SQL ?

Date and Time format in SQL

In SQL we have date and time format to show the date and time format in different ways lets see what are the date and time formats are there.

Date,Time,format,SQL
                               
                                                                                1.Date and Time format in SQL


FormateQuerySample
1select convert(varchar,getdate(),1)12/30/08
2select convert(varchar,getdate(),2)08.12.30
3select convert(varchar,getdate(),3)30/12/08
4select convert(varchar,getdate(),4)30.12.08
5select convert(varchar,getdate(),5)30-12-08
6select convert(varchar,getdate(),6)30 Dec 08
7select convert(varchar,getdate(),7)Dec 30 08
10select convert(varchar,getdate(),10)12-30-08
11select convert(varchar,getdate(),11)08/12/30
12select convert(varchar,getdate(),12)08 12 30
23select convert(varchar,getdate(),23)2008-12-30
101select convert(varchar,getdate(),101)12/30/2008
102select convert(varchar,getdate(),102)2008.12.30
103select convert(varchar,getdate(),103)30/12/2008
104select convert(varchar,getdate(),104)30.12.2008
105select convert(varchar,getdate(),105)30-12-2008
106select convert(varchar,getdate(),106)30 Dec 2008
107select convert(varchar,getdate(),107)Dec 30 2008
110select convert(varchar,getdate(),110)12/30/2008
111select convert(varchar,getdate(),111)2008/12/30
112select convert(varchar,getdate(),112)2008 12 30

Time only format:-

In SQL have some time only format lets see what are time format are there :-

Formate Query Sample
8 select convert(varchar,getdate(),8) 00:38:53
14 select convert(varchar,getdate(),14) 00:38:53:840
24 select convert(varchar,getdate(),24) 00:38:53
108 select convert(varchar,getdate(),108) 00:38:53
114 select convert(varchar,getdate(),114) 00:38:53:840


Read more »
By - No comments:
Labels: , , , , , , , , , ,

Monday, January 13, 2020

find nth highest salary without using top in sql ?

find , mysql
find nth highest salary without using top in sql ?

Find nth highest salary without using top in SQL

highest salary,nth highest salary,top
                                                     1.Find nth highest salary without using top sql
           Here i would use the CTC (common table expression).and i will use a one building function like Dense_Rank() to find the highest salary without using top.

Example:-     with result as 
                      (
                         select salary, dense_rank() over (order by salary desc) as denserank
                 from employee
                     )
                        select salary
                        from result
                        where result.denserank= n ( 1st,2st,3st.........nth)

Here we can get nth highest salary by using this query but end of the query we need to put the nth place which we want like if we want 1 highest salary we need to put 1 and 2  highest salary then put  2 like that we can find nth highest salary in this query.
                                                           But in our table duplicate records are there then above query will not give the perfect result then we should go for the next query. but here we need to use top () function

Example:-  with result as 
                      (
                         select salary, dense_rank() over (order by salary desc) as denserank
                 from employee
                     )
                        select  top 1 salary
                        from result
                        where result.denserank= n ( 1st,2st,3st.........nth)

Here i have another method to find nth highest salary without using top () function.

Example:-     select * from employee
                       order by salary desc offset n
                       fetch next 1 only

Here we used the offset which mean it will left the record and it will give the next record like we put offset 3 then it will left the top three (3) record and it will give the 4th record. in this process we can also find the nth highest salary without using top () function.
Read more »
By - No comments:
Labels: , , , , ,

how to find nth highest salary| max salary different ways ?

desc , highest salary
how to find nth highest salary| max salary different ways ?
nth highest salary,highest salary,max,
                                                     1.how to find nth highest salary| max salary
                                                                                                       different ways

 How to find max salary
 Syntax:- select max(col_name) from table_name
 Example:- select max(salary) from employee   

How to find 2nd highest salary
Example:- select max(salary) from employee
                   where salary<(select max(salary) from employee)

How to find top 2 highest salary
Example:- select distinct top 2 salary
                  from employee
                  order by salary desc

How to find nth highest salary using sub query
Example:- select top 1 salary from 
                  (select distinct top 1 salary
                   from employee
                   order by salary desc)
                   result
                   order by salary 

Here we used the sub query to find the nth highest salary but above query gives the top first salary but you needs 2nd,3nd,4nd,5nd...............salary we need to modify the query like this.you need 3nd highest salary then put  distinct top 3.....

 Example:-select top 1 salary from 
                   (select distinct top 1..(2nd,3nd,4nd,5nd....nth) salary
                   from employee
                   order by salary desc)
                   result
                   order by salary 


Read more »
By - No comments:
Labels: , , , , , , , , , , , , ,

Friday, January 10, 2020

SQL language Element ?

clause , constants
SQL language Element ?
SQL,element,datatype,identifiers,
                                                         1.SQL Language Element

SQL Language Element


  1. Identifiers

                      Names of database objects such as tables,views,columns,rows etc.    

      2. Datatypes      

                     
                      Define the type of data that is contain by columns.

      3.Constants


                       Symbols that represent specific data type.

      4.Operators


                       Perform arithmetic,comparison and logical operations.

      5.Function


                      There are two types of functions
                               1.Built-in function
                               2. User define  function

          1.Built-in Function

                                     Built-in  function is perform specific operations.in sql have only built-in                                               function.

          2.User define function 

                                     In this function user can create there own function but to create own function                                       they have some rules.

        6.clauses

                     Constituent component of statement and queries.
                      Example:- var clause

        7.Expression

                           Produce scalar values or tables containing of column and row of data.

        8. Queries

                       Retrieve the data based on specific criteria this is an important element of SQL.

        9.Statement

                          create database ABC
                     
                          create     :- SQL command
                          Database:-Database Keyword
                          ABC      :- User define object_name
                                                      
Read more »
By - 1 comment:
Labels: , , , , , , , , , , , , ,

What is DBMS.Advantage of DBMS.Types of DBMS.

advantage , child
What is DBMS.Advantage of DBMS.Types of DBMS.
DBMS,SQL,type if dbms,
                                            
                                                                    1.What is DBMS,Advantage,Type of DBMS

What is DBMS


  • A database management system is a software designed to assist in maintaining and utilizing large collection of data.
            Example:- Oracle,MySql,SQL.
  •  The alternative to use a DBMS is to store the data in files and write application specific to manage it.
  • DBMS allows users to create their own databases as per requirement.
  •  It provides an interface between the data and the software application.

Advantage of DBMS

  • Data independence. 
  • Efficient data access
  • Data integrity and security
  • Data Administration 
  • Concurrent access and data recovery
  • Reduce application development time

Types of DBMS 

  1. Hierarchical DBMS
  2. Network DBMS
  3. Relational DBMS
  4. Object Oriented

1.Hierarchical DBMS

  • In Hierarchical database, model data is organized in a tree like structure.
  • Data is stored hierarchically (top down or bottom down ) format .
  • Data is represented using a parent child relationship.
  • In hierarchical DBMS parent may have many children,but children have only one parent.

2.Network DBMS

  • The network database model allow each child to have multiple parents.
  • It helps you to address the need to model more complex relationship like many to many relationship.
  • In this model,entities are organized in a graph which can accessed through several paths.

3.Relational DBMS 

  • Relational DBMS is the widely used DBMS model in the industries because it is one of the easiest.
  • Relational DBMS based on the normalization data in the row and column of the tables.
  • It is stored in a fixed structures and manipulated using SQL.

4.Object Oriented 

  • In object oriented model data data stored in a form of objects. 
  • The structure we called is class we stored data within the class.
  • It define a database as a collection of objects which store both data members values and operations.

Read more »
By - 1 comment:
Labels: , , , , , , , , , , , ,

SEVER TYPE IN SSMS ( Sql Server Managment studio)

When you install the SQL Server management studio and then you want to connect with the SQL Server then there are four types of SQL servers ...