You can practise SQL live online for Free. Here are a list of some websites in no particular order. Oracle Live SQL. This site also has lots of examples and tutorials which you can see the codes right there and then SQL Fiddle: You can practise various SQL by major players here as well. w3schools:
Using Subqueries to join 2 more tables which do not have distinct values in a column We want to find the number of Flight bookings by Country, number of Car bookings by Country and the number of Hotel bookings by Country all appearing in one table with same country on one row. We want to
This is a short guide to getting ranking or rating from Microsoft Sql Server customers. Ranking is taken by checking how many times a customer has visited a particular country divided by the total number of travels the customer has made within the database
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 |
/*make a back up of the recommender rankings table*/ truncate table antrak.dbo.recommender_rankings_copy insert into antrak.dbo.recommender_rankings_copy select * from antrak.dbo.recommender_rankings /*select total number of times a customer has travelled to a specific country*/ select [First Name] ,Country , count(*) as [# times visited] into #travelpercountry FROM [Antrak].[dbo].[FlightBookingsMain] group by [First Name] ,Country order by [First Name] /*total number of times each customer has travelled to all destinations*/ select [First Name] , count(*) as [# times visited] into #totaltravel FROM [Antrak].[dbo].[FlightBookingsMain] group by [First Name] ----,Country order by [First Name] -- lets view by the total bookings select * from #totaltravel order by [First Name] -- lets view bookings per country select * from #travelpercountry order by [First Name] /*get rating*/ drop table antrak.dbo.recommender_rankings select tt.[First Name] ,Country ,cast(round((cast(tpc.[# times visited] as decimal(3,0)) / cast(tt.[# times visited] as decimal(3,0)) * 100), 1) as float) as rating into antrak.dbo.recommender_rankings from #totaltravel as tt inner join #travelpercountry as tpc on tt.[First Name] = tpc.[First Name] order by [First Name] select * from antrak.dbo.recommender_rankings /*lets refresh the temporary tables*/ drop table #totaltravel drop table #travelpercountry |
Sometimes, you would want to split values in a column into separate rows in SQL server. This is a simple guide. Lets check the fields in the table we will be working with.
|
1 2 3 4 5 6 7 |
select [orderID] ,[itemType] ,[price] ,[quantity] from [Antrak].[dbo].[ClothingSale] |
Let’s check the result: We can see that the itemType field has different values wheres as the price and quantity
Sometimes you want to get some numbers and also percentages for quick analysis. This is a quick simple way to get quick overviews
I receive a lot of emails about where to get quick and excellent resources for explaining most of the concepts of Transact-SQL (T SQL) and Microsoft SQL Server in general What is Transact-SQL or T SQL. T-SQL (Transact-SQL) is a set of programming extensions from Sybase and Microsoft that add several features to the Structured Query
Sometimes, you need some mock or test data to either test your program or for your analysis or to populate your database. Or sometimes, you need some sample or mock data to illustrate or explain a concept but you do not want to use your companies’ data or your delicate data. There are a
SQL Joins can sometimes be a bit confusing, particularly if you a newbie. I have below 2 visual representations which should help enhance your visual understanding of SQL JOINS First is from From CodeProject, by C L Moffatt You can check full explanation here: Sql Joins visually explained 2. Second was from Duke University.
When installing MySql server package, you can instruct the installation to configure MySql server to start automatically on each windows start up. However, if you want to reserve resources whilst you will not be working with the MySql server and hence you do not want the server to automatically start up on every boot of
Summarizing Data This week we are looking at five most common aggregate functions used to summarize information stored in tables: COUNT and SUM very frequently. COUNT is the only aggregate function that can work on any type of variable. The other four aggregate functions are only appropriate for numerical data. All aggregate functions require you
An experienced DevOps and Cloud Training Company to meet your DevOps and Cloud needs
You can see how this popup was set up in our step-by-step guide: https://wppopupmaker.com/guides/auto-opening-announcement-popups/
