Job specificaitons: SQL Database first time Job study guide

SQL Query: Group students by Age, Male, Female, and Total

I would like to create some statistics about my users.

I want to group them in based on intervals of their ages, and sum them up based on their genders. So in the end i get a table like this:

   age | male | female | total
  0-18 |    2 |      1 |     3
 18-25 |    3 |      4 |     7
25-100 |   13 |     25 |    38
 total |   18 |     30 |    48

(btw. these are example intervals)

Rightnow i do this by running this query for every row:

SELECT
    SUM(IF(gender = 'male', 1,0)) AS `male`,
    SUM(IF(gender = 'female', 1,0)) AS `female`,
    COUNT(gender) AS `total`
FROM `users`
WHERE 
    `birthday` 
        BETWEEN 
            DATE_SUB(CURDATE(), INTERVAL 25 YEAR) 
        AND 
            DATE_SUB(CURDATE(), INTERVAL 18 YEAR)

and sum the results up by my php script to create the last total row

how could I combine all these querys to improve the performance and maybe also create the last total line via mysql.

The intervals i want to use are:

0-18
18-25
25-31
31-36
36-41
41-46
46-51
51-56
56-61
61-100

Solution:

SELECT  Age,
        SUM(gender = 'female') Female,
        SUM(gender = 'male') Male,
        COUNT(*) TotalPerson
FROM
        (
            SELECT  CASE 
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 0 AND 18 THEN '00-18'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 19 AND 25 THEN '19-25'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 26 AND 31 THEN '26-31'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 32 AND 36 THEN '32-36'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 37 AND 41 THEN '37-41'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 42 AND 46 THEN '42-46'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 47 AND 51 THEN '47-51'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 52 AND 56 THEN '52-56'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 57 AND 61 THEN '57-61'
                        WHEN FLOOR(DATEDIFF(CURDATE(), birthday) / 365) BETWEEN 62 AND 100 THEN '62-100'
                    END Age,
                   Gender
            FROM   users
        ) ageList
GROUP   BY Age

• SQLFiddle Demo
• SQLFiddle Demo (WITH ROLLUP)

OUTPUT

╔════════╦════════╦══════╦═════════════╗
║  AGE   ║ FEMALE ║ MALE ║ TOTALPERSON ║
╠════════╬════════╬══════╬═════════════╣
║ 00-18  ║      0 ║    1 ║           1 ║
║ 19-25  ║      6 ║    5 ║          11 ║
║ 26-31  ║     22 ║    4 ║          26 ║
║ 32-36  ║     39 ║   16 ║          56 ║
║ 37-41  ║     20 ║   11 ║          31 ║
║ 42-46  ║     31 ║    6 ║          38 ║
║ 47-51  ║     18 ║    3 ║          21 ║
║ 52-56  ║     11 ║    6 ║          17 ║
║ 57-61  ║     10 ║    6 ║          16 ║
║ 62-100 ║     12 ║   12 ║          24 ║
╚════════╩════════╩══════╩═════════════╝

OUTPUT with using WITH ROLLUP

╔════════╦════════╦══════╦═════════════╗
║  AGE   ║ FEMALE ║ MALE ║ TOTALPERSON ║
╠════════╬════════╬══════╬═════════════╣
║ 00-18  ║      0 ║    1 ║           1 ║
║ 19-25  ║      6 ║    5 ║          11 ║
║ 26-31  ║     22 ║    4 ║          26 ║
║ 32-36  ║     39 ║   16 ║          56 ║
║ 37-41  ║     20 ║   11 ║          31 ║
║ 42-46  ║     31 ║    6 ║          38 ║
║ 47-51  ║     18 ║    3 ║          21 ║
║ 52-56  ║     11 ║    6 ║          17 ║
║ 57-61  ║     10 ║    6 ║          16 ║
║ 62-100 ║     12 ║   12 ║          24 ║
║ TOTAL  ║    169 ║   70 ║         241 ║
╚════════╩════════╩══════╩═════════════╝

Job Specifications: SSRS Developer Resume (CV)

SSRS Developer Resume Austin, TX

SQL DEVELOPERS RESUMES

• 
• 
• 

Please note that this is a not a Job Board - We are an I.T Staffing Company and we provide candidates on a Contract basis. If you need I.T.Professionals to fill a Contract Position, please call (800) 693-8939 or Submit a Request

inShare

Profile summary:

• Over 7 Years of experience with MS SQL Server 7.0/2000/2005 as a back end developer in financial, insurance and retail companies.
• Experienced in Database design, Coding, unit testing and Implementation.
• Proficient in creating Index, Views, Complex Stored Procedures and appropriate User defined functions.
• Expertise in database activities like Data Modeling, Database Design, Development, Database creation and Maintenance, Performance Monitoring and Tuning, Troubleshooting, Normalization, Replication and Documentation.
• Knowledgeable in designing XML documents using DTD, XML Schemas, XSLT and XPath queries for its integration in database.
• Experience in creating scheduling Jobs, Alerts, and SQL Mail Agent
• Good knowledge in developing and designing reports through SSRS and Excel
• Expert in Ad-hoc reports, drill down and drill through reports and cross tab reports.
• Involved in Migration from heterogeneous sources including Oracle to MS SQL Server
• Excellent technical and analytical skills with clear understanding of design goals of ER modeling for OLTP and dimension modeling for OLAP
• Involved in the creation of different scripts and functions using PL/SQL in ORACLE
• Involved in Partitioning for increase in Performance and availability of data.
• Scheduled DTS Packages and Involved in data extraction and loading using DTS in MS SQL Server 2000 and SSIS in MS SQL Server 2005,Import Export data, Bulk Insert and BCP.
• Expert in Data Extraction, Transforming and Loading (ETL) using For Each Loop in SQL Server Integration Services (SSIS) with .NET.
• Skilled in Developing SSAS Cubes, Dimensions, Facts, data source views. Deploying and Processing SSAS objects
• Involved in UAT and Integration testing with QA team.
• Organize professional with excellent Oral and written communication skills.
• Ability to work on fast paced work environment.
• Skilled at assessing client needs, working in groups, suggesting ideas that enhance efficiency and maximize performance, implementing technology solutions, and training /supporting end users.

Technical Skills:

Databases: SQL Server 2000/2005, Oracle 9i,10g,MySQL SQL Server Tools: DTS, BIDS(SSIS, SSAS, SSRS), BIMS, Query Analyzer, Enterprise manager Operating Systems: Windows 95/98/2000/2003/XP-Pro, Windows NT, Windows XP Programming Languages: T-SQL,PL/SQL,C#,VB.NET,C,C++,PHP, Java Script, CSS,HTML, XML Web Development Software: Adobe Dreamweaver, Adobe Flash, XAMP(software bundle), Microsoft Office Tools: Microsoft Word, Excel, Access and Visio, Visual Source Safe Reporting Tools: SSRS, Business Intelligence Tools, MS Access Graphics Editing Program: Adobe Photoshop

EXPERINECE

Confidential, Austin, TX Role: SSRS Developer Mar 08-Till Date

Confidential Corporation is a leading provider of best-of-breed products and services that are designed to improve the operating performance of financial institutions of all asset sizes and charters. This application provides online corporate credit card services to major merchants like DPS, Texas Pay Toll. The system provides the web-based interface for credit card transactions involved in their collection process.

Responsibilities:

• Developed and Optimized Stored Procedures, Views, and User-Defined Functions for the Application
• Developed Physical data models and created DML scripts to create database schema and database objects
• Employed Server Group Management, new to SQL Server 2008 which enables T-SQL queries to be issued against multiple servers from a single central management server.
• Performed Debugging on the Stored procedures for the requirement to handle the errors
• Employed Transparent Data Encryption, a new feature of SQL Server 2008 to store the data securely by encrypting the database files.
• Development of custom scripts and stored procedures for data import and manipulation
• Implemented Full Text Search for fast and efficient querying when large and unstructured data is encountered.
• Used SSIS to create ETL packages to validate, extract, transform and load data to data warehouse and data marts
• Migrated data from Heterogeneous Data Sources and legacy system (DB2, Access, Excel) to SQL Server databases using SQL Server Integration Services (SSIS) to overcome transformation constraints.
• Implemented Incremental load, used Event Handlers to clean the data from different data sources
• Generated periodic reports based on the statistical analysis of the data from various time frame and division using SQL Server Reporting Services (SSRS)
• Developed various operational Drill-through and Drill-down reports using SSRS
• Developed different kind of reports such a Sub Reports, Charts, Matrix reports, Linked reports.
• Used cascaded parameters to generate a report from two different Data Sets.
• Involved with Query Optimization to increase the performance of the Report.
• Developed Reports for business end users using Report Builder with updating Statistics.
• Involved in Analyzing, designing, building &, testing of OLAP cubes with SSAS 2005
• Interacting with the Database Administrator to maintain the database used DBCC Statements to perform maintenance tasks before backing up the databases.
• Database and Log Backups and Scheduling Backups by creating and deploying SSIS packages.
• Dealt with Fine-tuning Stored Procedures to improve performance with a Query plan using SQL Tuning advisor.
• Employed Filtered Indexes to improve query performance and reduce the storage and maintenance cost.
• Interacting with Business Analysts and Developers in identifying the requirements and designing and implementing the Database Schema
• Documented and maintained database system specifications, diagrams, and connectivity charts.

Environment: SQL Server 2000/2005/2008, Integration Services (SSIS), Reporting Services (SSRS), Analysis Services (SSAS), DTS, C#, .Net, T-SQL, Windows 95/NT/2000/2003, XML, MS Excel, MS Access, MS visual Studio, BIDS.

Confidential, San Antonio, TX Database Developer Aug 07 - Feb 08 Confidential Company is one of the largest, independently owned food retailers in the nation with hundreds of stores throughout Texas and more than 70,000 Partners (employees). Described by industry experts as a daring innovator and smart competitor, H-E-B has led the way with creative new concepts, outstanding service and a commitment to diversity in its workforce, workplace and marketplace. Responsibilities:

• Played an active role during the requirements gathering, analyzing and designing of the system.
• Working on Client/Server tools like SQL Server Enterprise Manager and Query Analyzer to Administer SQL Server.
• Created logical data model using ER/Win 4.0.
• Development of large-scale test databases and associated maintenance scripts.
• Development of custom scripts and stored procedures for data import and manipulation
• Configuring, tuning, and optimizing SQL Server 2005.
• Transformed data from various data sources using OLE DB connection by creating various DTSpackages.
• Used SQL Profiler and Index Tuning Wizard for tracing slow running queries and stored procedures.
• Proactive and Reactive tuning of databases in every stage of the project.
• Evaluated database performance and performed maintenance duties such as tuning, backup, restoration and disaster recovery.
• Created horizontal partitioning of data for different file groups.
• Develop reports using complex formulas and to query the database to generate different types of ad-hoc reports using SSRS.
• Production support, troubleshooting and debugging in a high volume transaction processing environment utilizing a lot of XML.
• Configured and trained users to use Reporting Services.
• Create parameterized Reports using Charts, Cross-Tab and Sub-report types.
• Responsible to support the issues with reports on the enterprise environments (Development, UAT, Test and Production).

Environment: Windows 95/NT/2000/2003, Microsoft SQL Server 2000, Microsoft Excel, ODBC, XML, SQL Query Analyzer, DTS, Erwin, Visual Source Safe, Reporting Services(SSRS), BIDS.

Confidential, Saint Louis, Missouri SQL Developer / DBA Feb 06 - Jul 07 Confidential is one of the nation's "Most Wired" health systems, according to the results of the 2007 Most Wired Survey and Benchmarking Study. This is Mercy's sixth year on the list. As a part of team, my responsibilities included writing store procedures, designing DTS and SSIS packages, migrating databases to Production environment, modifying database structures, providing support and guidance to operational models by checking for data quality and its consistency, developing database backup/recovery procedures, providing technical support. Responsibilities:

• Wrote and maintained database documentation, including data standards, procedures, definitions for the data dictionary and logical and physical models of the databases
• Designed DTS/SSIS Packages to transfer data between servers, load data into database, archive data file from different DBMSs using SQL Enterprise Manager/SSMS in SQL Server 2000/2005 environment, and deploy the data. Scheduled the jobs to do these tasks periodically.
• Daily, weekly, and monthly production data processing, monitoring, and trouble-shooting in an environment of operational systems
• Created, maintained, modified, and optimized SQL Server databases, database security and auditing.
• Developed SQL Reporting services web applications using .Net 3.5 principles.
• Developed custom components using .Net tools.
• Wrote stored procedures for report generation in SQL Server2005.
• Successfully deployed reports in various sources like XML, Web browser and PDF.
• Administrated the created Reports by assigning permission to the valid user for executing the reports.
• Created and Maintained Database Maintenance Plans.
• Configure and manage database maintenance plans for update statistics, database integrity check and backup operations.
• Installed and maintained database applications to improve processing performance including applying upgrades, patches, and bug fixes in all database instances
• Created scripts and SQL jobs to facilitate database backup and recovery, server and data processing monitoring and alerting
• Worked with developers to setup databases and coordinate the schemas and procedures deployment on staging and production.
• Maintain disaster recovery strategies for the database and failover methods.

Environment: SQL Server 2000/2005, Windows Server 2003, Windows NT, .NET, Erwin, DTS, BIDS.

Confidential, Overland Park, Kansas SQL Developer / DBA Aug 05 - Jan 06

Confidential offers a comprehensive range of wireless and wire line communications services bringing the freedom of mobility to consumers, businesses and government users. Sprint Nextel is widely recognized for developing, engineering and deploying innovative technologies, including two wireless networks serving almost 49 million customers at the end of the second quarter of 2009.

Responsibilities:

• Responsible for creating Databases, Tables, Cluster/Non-Cluster Index, Unique/Check Constraints Views, Stored Procedures, Triggers, Rules and Defaults
• Creation of New NT Logins and granting privileges to users/Groups and objects
• Used DBCC commands to check physical and logical consistency of the databases and Rebuild indexes
• Experience in Extraction Transformations and Loading (ETL) tools like Data Transformation Services (DTS), SSIS
• Backup and recovery of Master, MSDB and Application databases
• Monitor NT Event log for system, application errors, available disk space, Backup registry, keep emergency repair disk as current
• Responsible for Query optimization and Performance tuning
• Used NT Performance monitor to monitor CPU & Memory usage and SQL Server Profiler to monitor and record database activities of particular users, applications
• Worked extensively on DTS Package designs for Import/Export from various Data Sources like Flat file, Excel, MS Access to MS SQL Server and scheduling jobs in SQL Server.
• Provided 24 X 7 dedicated support for Production, QA & Development environments.
• Monitor Server Level Blocks/deadlocks and Maintain all the OLTP Servers with Maximum Database Availability, Reliability & Investigate and resolve any Database Related issues /performance related issues with Minimal Downtime and Maximum Reliability
• Monitor SQL Error Logs /Schedule Tasks/database activity/eliminate Blocking & deadlocks /user counts and connections/locks etc.
• Setup Transactional Replication and maintain the servers. Created Transactional Replication, PullSubscription and defined editions to transfer data from branch to head office and vice versa
• Migrated existing applications to SQL Server 2005
• Involved in SQL Server 2000/2005 installation and Upgrade in Test, Development, QA & Production servers

Environment: SQL Server 2000/2005, Windows NT, Windows Enterprise Server 2003, .NET, Erwin, Microsoft Visio, DTS, SQL Profiler, Query Analyzer, ETL

Confidential, Jackson, MS SQL Server Developer/DBA Aug 04 - Jul 05 Confidential provides banking, wealth management and risk management solutions. For more than 100 years, Confidential has offered premier financial services to the market it serves. With locations in Florida, Mississippi, Tennessee and Texas, Confidential continues to expand its customer relationships and prove that Confidential is "People you Trust. Advice that Works". Responsibilities:

• Performance tuning, optimization of procedures and queries and also support, maintain, monitor and tune high available and clustered database systems.
• Extensively used T-SQL in constructing user functions, views, indexes, user profiles, relational database models, data dictionaries, and data integrity.
• Interacted with end users in identifying the requirements and developed a detailed design document including technical specification.
• Developed logical and physical database models.
• Developed a security schema for user access and maintained the databases by setting up logins, privileges etc.
• Developed strategy for database backup and recovery based on user requirements and implemented the same
• Wrote several SQL Scripts such as finding long running queries, blocking sessions, archiving data from production to archive server and populating reporting data.
• Monitoring SQL Server logs to ensure the backup operations, batch commands, or other scripts and processes have completed successfully.
• Created DTS packages for transforming data using ActiveX Scripts.
• Used Microsoft Visual Source Safe to check in and checkout projects
• Developed procedures and triggers involving extensive use of T-SQL to implement business rules
• Interacted with Business Analysts and Project Managers to give status updates and recommend changes
• Interacted in weekly meetings with IT Manager to give status updates on projects.
• Utilized documentation prepared by Business Analyst to complete projects.
• Used DTS, ETL and SSIS as needed.

Environment: SQL Server 2000, Windows 95/NT/2000/2003, DTS, SSIS, MS Visual Source Safe, T-SQL

Confidential, India Client: GE COMFIN DB Developer Aug 02 - Jul 04 Confidential is a BPO company in India which was formerly known as General Electric Capital International Services(GECIS) provides services and solutions to various businesses of the General Electric Company in custom application development, software engineering, Enterprise solutions, E-Business, system integration, technology consulting . Responsibilities:

• Participated in Requirement gathering and analysis to check feasibility of data and created physical and logical data models for walkthroughs.

• Performed all aspects of database administration, including data modeling, backups andrecovery.
• Running DBCC consistency checks, and fixing data corruption in application databases
• Database capacity planning and space forecasting.
• Created and Maintained Database Maintenance Plans.
• Designed cubes with star schema using Microsoft Analysis Services.
• Understanding the OLAP processing for changing and maintaining the Warehousing Optimizing Dimensions, Hierarchies and adding the Aggregations to the Cube.
• Experience writing T-SQL scripts, dynamic SQL, complex stored procedures, functions, triggers,SQLCMD, and SMO.
• Served as a SSRS 2000 Reports Developer and SQL Server Developer in support of Service Management System
• Maintained and implemented the database using SQL Server 2000 back-end tools and DTS.
• Design and implementation of Security Model, policies and procedures
• Creating and Modifying T-SQL stored procedures/triggers for validating the integrity of the data.
• Performing query plans and making sure each and every query is using appropriate useful indexes.
• Used Performance Monitor and SQL Profiler for Monitoring memory, processor, Disk I/O, SQL Queries and others.
• Rebuilding Indexes and Tables as part of performance tuning exercise
• Set up some tables in the main server as a snapshot publication and push them to the warehouse server (Subscriber) on the nightly basis. This eliminated the old way of emailing spreadsheet on the weekly basis.

Environment: SQL Server, T-SQL, Query Analyzer, Outlook, DTS, BCP, Enterprise Manager and Windows 2000 Server.