SQL Server – System DBs and Data Types

Two type of database

 

System Database and User Database. System database is again classified into

Master,Model, TempDb, and MSDB

 

 

 

Advertisements

Query to Get Column name and DataType

SELECT column_name,data_type
FROM information_schema.columns
WHERE table_schema = ‘public’
AND table_name = ‘Table Name’

 

output :

“id”;”bigint”
“unitid”;”bigint”
“itemcode”;”character varying”
“brandname”;”character varying”
“strength”;”character varying”
“itemname”;”character varying”
“moleculename”;”bigint”

Sql Data Type and Java Data Type Mapping

Sql Type                                                       Simply mappable                        Object Mappable

CHARACTER String
VARCHAR String
LONGVARCHAR String
NUMERIC java.math.BigDecimal
DECIMAL java.math.BigDecimal
BIT boolean Boolean
TINYINT byte Integer
SMALLINT short Integer
INTEGER int Integer
BIGINT long Long
REAL float Float
FLOAT double Double
DOUBLE PRECISION double Double
BINARY byte[]
VARBINARY byte[]
LONGVARBINARY byte[]
DATE java.sql.Date
TIME java.sql.Time
TIMESTAMP java.sql.Timestamp

SELECT DISTINCT clause

The syntax of the DISTINCT clause is as follows:

The DISTINCT clause is used in the SELECT statement to remove duplicate rows from a result set. The DISTINCT clause keeps one row for each group of duplicates. You can use the DISTINCT clause on one or more columns of a table.

SELECT DISTINCT
column_1
FROM table_name
SELECT DISTINCT
column_1,
column_2
FROM
tbl_name;
Example :
Distinct
SELECT DISTINCT  bcolor  FROM t1 ORDER BY bcolor;
Distinct_OP.PNG

PostgreSQL DISTINCT on multiple columns

SELECT DISTINCT  bcolor, fcolor FROM t1 ORDER BY bcolor,      fcolor;
distinct2.PNG
Because we specified both bcolor and fcolor columns in the SELECT DISTINCT clause, PostgreSQL combined the values in both bcolor and fcolor columns to evaluate the uniqueness of rows.

PostgreSQL DISTINCT ON ORDER BY example

The following statement sorts the result set by the  bcolor and  fcolor, and then for each group of duplicates, it keeps the first row in the returned result set.

SELECT DISTINCT ON (bcolor) bcolor, fcolor FROM t1 ORDER BY bcolor, fcolor;
distinct3.PNG

PostgreSQL Server and Database Objects

 

Server service

When you install a PostgreSQL instance, you will have a corresponding PostgreSQL server service.

Database

A database is a container of other objects such as tables, views, functions, indexes, etc. You can create as many databases as you want inside a PostgreSQL server.

Table

The table is used to store the data. You can have many tables in a database. A special feature of PostgreSQL table is inheritance. Meanings a table (child table) can inherit from another table (parent table) so when you query data from the child table, the data from parent table is also showing up.

Schema

A schema is a logical container of tables and other objects inside a database. Each PostgreSQL database may have multiple schemas. It is important to note that schema is a part of ANSI-SQL standard.

Function

The function is a block reusable SQL code that returns a scalar value of a list of records. In PostgreSQL, functions can also return composite objects.

Cast

Casts enable you to convert one data type into another data type. Casts actually backed by functions to perform the conversion. You can also create your own casts to override the default casting provided by PostgreSQL.

 

PostgreSQL

PostgreSQL is free and open source software. Its source code is available under PostgreSQL license, a liberal open source license. You are free to use, modify and distribute PostgreSQL in any form.

PostgreSQL requires very minimum maintained efforts because of its stability.  Therefore, if you develop applications based on PostgreSQL, the total cost of ownership is low in comparison with other database management systems

What make PostgreSQL stand out

-multi-version concurrency control (MVCC) feature

-It allows you to add custom functions developed using different programming languages such as C/C++, Java, etc.

-you can define your own data types, index types, functional languages, etc.

check PostgreSQL Version

select version()

 

Database Questions and Answers

This set of Database Questions & Answers focuses on “Join Operations – 2”

1. A_____ is a query that retrieves rows from more than one table or view:
a) Start
b) End
c) Join
d) All of the mentioned

2.Which product is returned in a join query have no join condition:
a) Equijoins
b) Cartesian
c) Both
d) None of the mentioned

3.Which is a join condition contains an equality operator:
a) Equijoins
b) Cartesian
c) Both and b
d) None of the mentioned

4.Which operation are allowed in a join view:
a) UPDATE
b) INSERT
c) DELETE
d) All of the mentioned