Code Storage: sql

Showing posts with label sql. Show all posts

Friday, January 17, 2020

Date and Time format in SQL ?

codestorage date , Date and time formate

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.

1.Date and Time format in SQL

Formate	Query	Sample
1	select convert(varchar,getdate(),1)	12/30/08
2	select convert(varchar,getdate(),2)	08.12.30
3	select convert(varchar,getdate(),3)	30/12/08
4	select convert(varchar,getdate(),4)	30.12.08
5	select convert(varchar,getdate(),5)	30-12-08
6	select convert(varchar,getdate(),6)	30 Dec 08
7	select convert(varchar,getdate(),7)	Dec 30 08
10	select convert(varchar,getdate(),10)	12-30-08
11	select convert(varchar,getdate(),11)	08/12/30
12	select convert(varchar,getdate(),12)	08 12 30
23	select convert(varchar,getdate(),23)	2008-12-30
101	select convert(varchar,getdate(),101)	12/30/2008
102	select convert(varchar,getdate(),102)	2008.12.30
103	select convert(varchar,getdate(),103)	30/12/2008
104	select convert(varchar,getdate(),104)	30.12.2008
105	select convert(varchar,getdate(),105)	30-12-2008
106	select convert(varchar,getdate(),106)	30 Dec 2008
107	select convert(varchar,getdate(),107)	Dec 30 2008
110	select convert(varchar,getdate(),110)	12/30/2008
111	select convert(varchar,getdate(),111)	2008/12/30
112	select 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

find nth highest salary without using top in sql ?

codestorage find , mysql

Find nth highest salary without using top in SQL

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.

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

codestorage desc , highest salary

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

SQL language Element ?

codestorage clause , constants

1.SQL Language Element

SQL Language Element

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

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

codestorage advantage , child

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

Hierarchical DBMS
Network DBMS
Relational DBMS
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.

Database fundamental.what is database and table and record.

codestorage attributes , column

1. Database fundamental,table,record

DataBase Fundamental

Database server instance contains multiple database.
Database contain multiples tables.
Data have two important form:

1. File form

2. Table form

1. File Form

SQL server database have three types of file:

a.Primary File

b.Secondary File

c. Transaction Log

a.Primary File:-

The primary data file contains the startup information for the database and point to the others file in database.
User data and object can be stored in this file or secondary data file.
Every database has one primary data file.
The recommended file name extension for primary data file is ".mdf ".

b.Secondary File:-

Secondary data files are optional,are user defined and store user data.
Secondary files can be used to spread data across multiple disk by putting each file on a different disk drive.
If a database exceeds the maximum size for a single windows file you can use secondary data files so the database can continue grow.
The recommended file name extension for secondary data files is ".ndf".

c.Transaction Log:-

The transaction log files hold the log information that is used to recover the database.
There must be at least one log file for each database.
The recommended file name extension for transaction log is ".ldf".

2.Table Form

SQL table is a collection of data which is organized in terms of rows and columns.
In DBMS,the table as known as relation and row as a tuples.
Table is a simple form of data storage.A table is also convenient representation of relation.
Tables contains multiple records (Rows and Columns ).

What is Database

A database is a systematic collection of data .
Database support storage and manipulation of data.
Database make data management easy.

Table And Record

A table in a relational database is a predefined format of rows and column that define an entity.
Each column contains a different type of attribute and each row corresponds to a single record.
Each table is provided with a name.

Example:-

SID Name Address Contact

1001 Shiva Delhi 123456

1002 Ram Mumbai 987658

1003 Sri Pune 435675 --------> Record(Row)

What is SQL.Purpose of SQL.Who should learn

codestorage backend. , database

1. What is SQL and Purpose of SQL

Introduction of SQL

SQL stands for structured query language. SQL was initially developed at IBM is 1970s.
SQL is the the standard language to communicate with relational database management system like oracle,MS access,MS SQL server, Mysql,Db2,Sybase etc.SQL is a command based language.
SQL is domain specific not general purpose,used in design and management of data held in RDBMS ( Relational database management system).
SQL is a command based language.
SQL lets you access and manipulate databases.
SQL became a standard of the american national standard institute (ANSI) in 1986,and of the international organization for standardization(ISO) in 1987.

Purpose of SQL

SQL is used to create new database.
SQL is used to create new tables in a database.
SQL is used to insert records, update records,delete record in a database.
SQL is used to retrieve data from database.
SQL can set permission on tables procedures and views.
SQL is used to create stored procedures in a database.
SQL is used to create view in a database.

Who should learn SQL

Database Developers.
Database Admistrators.(DBA)
Database Testers.

Database Developers:

Design and deploy database table structures,forms,reports,and queries.

Database Admistrators:

Keeping database upto data and managing database access.writing reports documentation and operating manulas.

Database Testers:

Verify data Integrity ( completeness and correctness of data).
Verify data Manipulation (Add,Update,Delete).
Verify data Comparison (two database front end data with back end data).

Advertisement

Friday, January 17, 2020

Date and Time format in SQL

Time only format:-

Monday, January 13, 2020

Find nth highest salary without using top in SQL

Friday, January 10, 2020

SQL Language Element

Identifiers

2. Datatypes

3.Constants

4.Operators

5.Function

1.Built-in Function

2.User define function

6.clauses

7.Expression

8. Queries

9.Statement

What is DBMS

Advantage of DBMS

Types of DBMS

1.Hierarchical DBMS

2.Network DBMS

3.Relational DBMS

4.Object Oriented

Thursday, January 9, 2020

DataBase Fundamental

1. File Form

a.Primary File:-

b.Secondary File:-

c.Transaction Log:-

2.Table Form

What is Database

Table And Record

Monday, January 6, 2020

Iphone 11

offer in Iphone

Mobile

Follow Us

Facebook

About Me

Search This Blog

Recent

Featured

Dell Inspiron

Hp Pavilion

Subscribe Us

Blog Archive

Ikigai

Think And Grow Rich

Recent Post

Apple mac Pro

Tags

Samsung S10

Recent Comments