چه تکنولوژی می‌خواهید یاد بگیرید؟

نظرات و انتقادات خود را با ما در میان بگذارید

آپدیت روزانه

بیش از 1500 مطلب آموزشی

نیازهای روز

37 کوئری پایه SQL برای کاربران مبتدی

SQL مخفف عبارت Structured Query Language یا زبان جستجو ساخت یافته برای پرس و جو بانک اطلاعاتی است که قابلیت دسترسی به اطلاعات پایگاه داده را می‏دهد، در واقع SQL یک استاندارد بین الملی است. در مطلب 37 کوئری پایه SQL برای کاربران مبتدی با مثال‏های کاربردی CRUD آشنا خواهید شد.

-- Get all columns and all rows from ‘Product’ table
SELECT * FROM Product 

-- Get Column with their respective values from the table
SELECT ProductName, Qty, Price FROM Product 

-- Get all columns from ‘Product’ table with where clause
SELECT * FROM Product WHERE ProductId=2 

-- Get all columns from ‘Product’ table  using Between Operator
SELECT * FROM Product WHERE ProductId Between 4 and 8  

-- Get all rows from ‘Product’ table where ProductName is Mouse,Keyboard,USB Stick 
SELECT * FROM Product WHERE ProductName in ('Mouse','Keyboard','USB Stick')  

-- Get all rows from ‘Product’ table where the name starts with ‘K’ letter
SELECT * FROM Product WHERE ProductName Like 'K%'   

-- Get all rows from ‘Product’ table where the name ends with ‘K’ letter
SELECT * FROM Product WHERE ProductName Like '%K' 

-- Get all rows from ‘Product’ table where the name starts with ‘E/F’ letter
SELECT * FROM Product WHERE ProductName Like '[EF]%'    

-- Get all rows from ‘Product’ table where the name not starts with ‘E/F’ letter
SELECT * FROM Product WHERE ProductName Like '[^EF]%' 

-- Get single column with a combination of Product and Qty added together with a space from Product’ table
SELECT (ProductName + SPACE(1) + CONVERT(VARCHAR,Qty)) AS ProductWithQty FROM Product 

-- Get all rows from ‘Product’ table where ProductName column is not containing null values 
SELECT * FROM Product WHERE Description IS  NOT NULL 

-- Get all rows from ‘Product’ table in descending order of ProductId 
SELECT * FROM Product ORDER BY ProductId DESC 

-- Get all rows from ‘Product’ table in ascending order of ProductId 
SELECT * FROM Product ORDER BY ProductId 

-- Get Unique row from ‘Product’ table based on CategoryId 
SELECT DISTINCT CategoryId FROM Product 

-- Get top 5 Rows from ‘Product’ table 
SELECT Top 5 * FROM Product 

-- Get maximum value Of ProductId from ‘Product’ table 
SELECT MAX(ProductId) FROM Product 

-- Get maximum value Of Qty from ‘Product’ table 
SELECT * FROM Product COMPUTE MAX(Qty) 

-- Get even row from table 'Product' 
SELECT Temp.* FROM(  
              SELECT ROW_NUMBER() 
              OVER (ORDER BY ProductId) AS row_num,   Product.* FROM Product) Temp 
              WHERE ((Temp.row_num) % 2)  = 0 

-- Get odd row from table 'Product' 
SELECT Temp.* FROM (SELECT ROW_NUMBER() OVER (ORDER BY ProductId) AS row_num, Product.* FROM Product) Temp 
              WHERE ((Temp.row_num) % 2)  = 1 

-- Count number of records in a 'Product' table 
SELECT COUNT(*) AS RecordCount FROM Product 

-- List all the categories with products (Inner Join) 
SELECT * FROM Category INNER JOIN Product ON Category.CategoryId = Product.CategoryId 

-- List all rows from category, even if there are no matches in the product table (Left Join) 
SELECT * FROM Category LEFT JOIN Product ON Category.CategoryId = Product.CategoryId 

-- List all the rows from product, even if there are no matches in the category table (Right Join) 
SELECT * FROM Category RIGHT JOIN Product ON Category.CategoryId = Product.CategoryId 

-- List all the rows from the category table and all the rows from the product table (Full Join) 
SELECT * FROM Category FULL JOIN Product ON Category.CategoryId = Product.CategoryId 

-- Get average value of price from product table 
SELECT AVG(Price) AS PriceAverage FROM Product 

-- Get number of products from product table 
SELECT COUNT(ProductName) AS NoOfProducts FROM Product 

-- Get Maximum price of product from product table 
SELECT MAX(Price) AS MaximumPrice FROM Product 

-- Get minimum price of product from product table 
SELECT MIN(Price) AS MinimumPrice FROM Product 

-- Get total number of qty of products from product table 
SELECT SUM(Qty) AS TotalProducts FROM Product 

-- Get all product name is in upper case from product table 
SELECT UPPER(ProductName) AS PRODUCTS FROM Product 

-- Get all product name is in lower case from product table 
SELECT LOWER(ProductName) AS products FROM Product 

-- List all columns of all tables in a database 
SELECT sys.objects.name AS TableName,sys.columns.name AS ColumnName  
                           FROM sys.columns  INNER JOIN sys.objects ON sys.columns.object_id=sys.objects.object_id and 
						   type_desc = 'USER_TABLE'  
						   ORDER BY sys.objects.name,sys.columns.column_id 

-- List all tables which have column name like 'CategoryId' 
SELECT sys.tables.name AS table_name, SCHEMA_NAME(schema_id) 
					   AS schema_name,  sys.columns.name AS column_name 
				       FROM sys.tables  INNER JOIN sys.columns   
					   ON sys.tables.OBJECT_ID = sys.columns.OBJECT_ID  
					   WHERE sys.columns.name LIKE '%CategoryId%'  
					   ORDER BY schema_name, table_name  

-- List number of records in each table in a database 
DECLARE @DSql VARCHAR(MAX)  
SELECT @DSql = COALESCE(@DSql + CHAR(13) + ' UNION ALL ' + CHAR(13), '') + 'SELECT ' + 
			   QUOTENAME(TABLE_NAME,'''') + ' as [Table Name], COUNT(*) AS [Records Count] FROM ' + 
			   QUOTENAME(Table_schema) + '.' + 
			   QUOTENAME(TABLE_NAME) FROM     INFORMATION_SCHEMA.TABLES 
			   ORDER BY TABLE_NAME   
			   EXEC( @DSql) 

-- List all tables in a database 
SELECT Table_Schema AS [Table Schema], Table_Name AS [Table Name]  
                FROM INFORMATION_SCHEMA.Tables 

-- List all procedures from a database 
SELECT name,create_date, modify_date FROM sys.procedures 

-- Generate xsd of a table with data 
DECLARE @XsdSchema XML  
SET @XsdSchema = (SELECT * FROM Category FOR XML AUTO, ELEMENTS, XMLSCHEMA('TestXsdSchema'))  
SELECT @XsdSchema 

امید نصری

0 نظر:

تعداد دیدگاه‌های کاربران : 0 دیدگاه
مهمان گرامی! برای ارسال نظر نیاز است وارد سایت شوید.


You must log on to comment.